Or logic in a slicer and a DAX measure

[Kani18]

Hi everybody

I am struggling with finiding a measure that can answer my questions.
I have a huge number of data whcih simply is a table looking like this:

What I would like to have for example is :
if I choose a and b on slicer Y, it performs in an "or" logic, and I have an output table like this:

This shows combination of a and b, come with c in three types of X : 11,22,33.
and they come with d in only two types of X which are 11,44.
and a and b alone come with four types of X which are 11,22,33,44.

I would be grateful if any one can help me building this or let me know if Power BI at all is able to build these three output columns?

Many thanks in advance

Kani

[Zubair_Muhammad · Accepted solution]

Hi @Kani18

This looks a tough one

See if this helps

Assuming your TableName is Table1

First create a calculated table to be used as slicer for selecting "Y" values

Table_For_Slicer = VALUES(Table1[Y])

Now we create a measure to be used a VISUAL FILTER
So whatever we select from above slicer the Table would show other "Y" values

Check_Measure =
IF ( SELECTEDVALUE ( Table1[Y] ) IN VALUES ( Table_For_Slicer[Y] ), 1, 0 )

[Zubair_Muhammad · Accepted solution]

@Kani18

Now we can use this MEASURE

List of X =
VAR mycount =
    COUNTROWS ( VALUES ( Table_For_Slicer[Y] ) )
VAR temp =
    FILTER ( ALL ( Table1 ), Table1[Y] IN VALUES ( Table_For_Slicer[Y] ) )
VAR temp1 =
    SUMMARIZE (
        temp,
        [X],
        "Count", COUNTX ( FILTER ( temp, [X] = EARLIER ( [X] ) ), 1 )
    )
VAR temp2 =
    FILTER ( temp1, [Count] = mycount )
VAR temp3 =
    SUMMARIZE ( temp2, [X] )
VAR temp4 =
    INTERSECT ( VALUES ( Table1[X] ), temp3 )
RETURN
    IF (
        HASONEFILTER ( Table1[Y] ),
        CONCATENATEX ( temp4, [X], ", " ),
        CONCATENATEX ( temp1, [X], ", " )
    )

[Zubair_Muhammad · Accepted solution]

Hi @Kani18

This looks a tough one

See if this helps

Assuming your TableName is Table1

First create a calculated table to be used as slicer for selecting "Y" values

Table_For_Slicer = VALUES(Table1[Y])

Now we create a measure to be used a VISUAL FILTER
So whatever we select from above slicer the Table would show other "Y" values

Check_Measure =
IF ( SELECTEDVALUE ( Table1[Y] ) IN VALUES ( Table_For_Slicer[Y] ), 1, 0 )

[Zubair_Muhammad · Accepted solution]

@Kani18

Now we can use this MEASURE

List of X =
VAR mycount =
    COUNTROWS ( VALUES ( Table_For_Slicer[Y] ) )
VAR temp =
    FILTER ( ALL ( Table1 ), Table1[Y] IN VALUES ( Table_For_Slicer[Y] ) )
VAR temp1 =
    SUMMARIZE (
        temp,
        [X],
        "Count", COUNTX ( FILTER ( temp, [X] = EARLIER ( [X] ) ), 1 )
    )
VAR temp2 =
    FILTER ( temp1, [Count] = mycount )
VAR temp3 =
    SUMMARIZE ( temp2, [X] )
VAR temp4 =
    INTERSECT ( VALUES ( Table1[X] ), temp3 )
RETURN
    IF (
        HASONEFILTER ( Table1[Y] ),
        CONCATENATEX ( temp4, [X], ", " ),
        CONCATENATEX ( temp1, [X], ", " )
    )

[Zubair_Muhammad · Accepted solution]

@Kani18

Please see attached file aas well

[Kani18]

This is amazing Zubair! Thank you very much for putting an effort on it and solved it such nicely! It works now very well

God bless!