Forum Discussion

hosea_chumba's avatar
hosea_chumba
Icon for Helper I rankHelper I
3 years ago

Dax Formula

Take the example below, Please assist to come up with a formula that identifies the common items appearing in all the months.  Date (DD/MM/YYYY) Item 01/08/2022 X 03/08/2022 Y 01/09/...
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi hosea_chumba 
Apologies for the late reply.

Please refer to attached sample file and below description. Hopping this is what you're looking for.

Place the following filter measure in the filter pane of the table or chart visual and select "is" 1 or "is not blank"

Filter Measure = 
VAR T1 = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Item] ) )
VAR T2 = SELECTCOLUMNS ( T1, "@YearMonth", FORMAT ( [Date], "YYYYMM" ) )
VAR T3 = SELECTCOLUMNS ( ALLSELECTED ('Table'[Date] ), "@YearMonth", FORMAT ( [Date], "YYYYMM" ) )
VAR REsult =
    IF ( 
        ISEMPTY ( EXCEPT ( T3, T2 ) ),
        1
    )
RETURN 
    Result