CODEWITHSUNDEEP
powerbidax
SarahChapman
SarahChapman

Reputation: 65

PowerBI Dax Measure - Aggregate data using If and Multiple Conditions

I am having issues trying to aggregate data with multiple conditions in PowerBI Dax.

I have a PowerBI Dataset similar to the one below, which lists Call ID and multiple vehicle codes and am trying to create a measure that acts as a flag using the logic below:

Dataset:

CallID 1 1 1 2 2 2 3 3 3

Result:

enter image description here

Any help or advise would be much appreciated. Thanks

Upvotes: 1

Views: 324

Answers (2)

Ozan Sen
Ozan Sen

Reputation: 2615

Please try this code:

H HX Flag =
VAR H_List =
    CALCULATETABLE ( VALUES ( TTable[CALLID] ), TTable[VEHICLE_CODE] = "H" )
VAR HX_List =
    CALCULATETABLE ( VALUES ( TTable[CALLID] ), TTable[VEHICLE_CODE] = "HX" )
RETURN
    COUNTX ( INTERSECT ( H_List, HX_List ), [CALLID] )

If we test it:

DFR

Do not forget to click the down-pointing arrow on CALLID column and ensure that "show items with no data" is checked. See the below picture:

DESR

Upvotes: 2

Jos Woolley
Jos Woolley

Reputation: 9052

Try this measure:

H HX Flag = 
VAR T1 =
    SUMMARIZE(
        'Table',
        'Table'[CALLID],
        "Flag",
            INT(
                COUNTROWS( FILTER( 'Table', 'Table'[VEHICLE_CODE] IN { "H", "HX" } ) ) = 2
            )
    )
RETURN
    IF( HASONEVALUE( 'Table'[CALLID] ), MINX( T1, [Flag] ), SUMX( T1, [Flag] ) )

The subtotal will give you the number of IDs flagged.

Upvotes: 0

Related Questions