Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
4 years ago
Solved

Calculated Column Count same table multiple filters

Hi experts!

I have a table that shows me the status per sales order for specific regions:

Sales OrderRegionStatus
100ADBZ
101ADBZ
100ADBX
101ADBX
102PRIO

 

Now I would like to create a calculated column that shows me per Sales Order if there is an entry for a Sales Order and Region combination with the value 'X'. If so, all rows with this combination should get TRUE, otherwise FALSE.

How is this possible?

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi joshua1990 
    Please use the following

     

    Flag = 
NOT ISEMPTY (
    CALCULATETABLE ( 
        Sales, 
        ALLEXCEPT ( Sales, Sales[Sales Order], Sales[Region] ),
        Sales[Status] = "X" 
    )
)

     

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi joshua1990 
    Please use the following

     

    Flag = 
NOT ISEMPTY (
    CALCULATETABLE ( 
        Sales, 
        ALLEXCEPT ( Sales, Sales[Sales Order], Sales[Region] ),
        Sales[Status] = "X" 
    )
)

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

    Condition CC = 
VAR _currentsalesorder = Data[Sales Order]
VAR _currentregion = Data[Region]
VAR _newtablecondition =
    "X"
        IN SUMMARIZE (
            FILTER (
                Data,
                Data[Sales Order] = _currentsalesorder
                    && Data[Region] = _currentregion
            ),
            Data[Status]
        )
RETURN
    DIVIDE ( _newtablecondition, _newtablecondition ) + 0