Dax calculated column

Question

I have a table with OrderNo and ProductCategory. One order can have many order lines. One order can also have different ProductCategories on the lines. The table looks like this:

OrderNo  ProductCategory
O1       Cat1
O1       Cat2
O1       Cat3
O2       Cat1
O3       Cat4

I would like a calculated column "CalcC" that returns 1 if Cat1 exists for an OrderNo. I want the 1 to be returned for all rows with that OrderNo.

My result table would look like this:

OrderNo ProductCategory CalcC
O1      Cat1            1
O1      Cat2            1
O1      Cat3            1
O2      Cat1            1
O3      Cat4            0

If someone can help me achieve this with some DAX syntax, I would be most grateful.

Thank you.

greggyb greggyb · Accepted Answer · 2016-02-09T21:48:50

CalcC =
IF(
    ISEMPTY(
        CALCULATETABLE(
            'FactOrder'
            ,ALLEXCEPT( 'FactOrder', 'FactOrder'[OrderNo] )
            ,'FactOrder'[ProductCategory] = "Cat1"
        )
    )
    ,0
    ,1
)

IF() is trivial. ISEMPTY() checks whether a table has 0 or >0 rows. CALCULATETABLE() evaluates a table in argument 1 based on the filter context defined in arguments 2-N. ALLEXCEPT() clears context from the named table, preserving it only on named columns.

Our CALCULATETABLE() returns 'FactOrder' only where [OrderNo] is the same as exists in the calling row context. All filter arguments are combined in a logical and, so in addition to restricting the table to those that share an [OrderNo] with the calling row context, we also restrict it to rows where [ProductCategory] = "Cat1".

If that table is empty, we return 0, else we return 1.

Dax calculated column

1 Answers