DAX Compare rows based on column values

yellowplum 0
Viewed 49

I am having the following problem and am completely new to DAX, so I don't know how to solve it. Should be pretty easy probably.

I have a table looking like this and I want to check for several values whethey they match for filter_id

id  filter_id
1   5
1   17
2   5
3   5
3   17
4   9
4   17
5   17

For example, I want to get all ids which contain filter_id 5 and filter_id 17. Since the data is stored in different rows, I cannot compare with and, since one row cannot possibly have both values. The result should be

id 1 and id 3

since only they have both values.

Thanks a lot for your help!

comparison dax
1 Answers

Create a separate table (Selected_FilterIDs) which comprises a single column (filter_id) containing your chosen filter_ids (e.g. 5 and 17).

You can then use the following measure:

MyMeasure :=
VAR SelectedValues =
    VALUES( Selected_FilterIDs )
VAR SelectedValuesCount =
    COUNTROWS( SelectedValues )
VAR T1 =
    SUMMARIZE(
        Main_Table,
        Main_Table[id],
        "MyCount",
            SUMX(
                DISTINCT( Main_Table[filter_id] ),
                IF( Main_Table[filter_id] IN SelectedValues, 1 )
            )
    )
RETURN
    CONCATENATEX(
        FILTER( T1, [MyCount] = SelectedValuesCount ),
        Main_Table[id],
        ", "
    )
Jos Woolley0
Related