Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kani18
Regular Visitor

Or logic in a slicer and a DAX measure

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:

a1.jpg

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:

a2.jpg
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

3 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

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 )

orlogic.png


Regards
Zubair

Please try my custom visuals

View solution in original post

@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], ", " )
    )

orlogic1.png


Regards
Zubair

Please try my custom visuals

View solution in original post

@Kani18

 

Please see attached file aas well


Regards
Zubair

Please try my custom visuals

View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

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 )

orlogic.png


Regards
Zubair

Please try my custom visuals

@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], ", " )
    )

orlogic1.png


Regards
Zubair

Please try my custom visuals

@Kani18

 

Please see attached file aas well


Regards
Zubair

Please try my custom visuals

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.