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
dom_oab
Advocate II
Advocate II

Filter on ALL selected values (instead of default AT LEAST ONE of selected values)

Hi all,

 

I have the following table:

Flight NumberFailure Symptom
1Symptom 1
1Symptom 2
1Symptom 3
2Symptom 1
2Symptom 3
3Symptom 2
3Symptom 4
4Symptom 3
4Symptom 4
5Symptom 1
5Symptom 2
5Symptom 5

 

I have a slicer for Symptom in my page.

 

I need to display all flights with symptoms occurring along with ALL selected symptoms.

 

But by default Power BI shows all flights having AT LEAST ONE of selected values.

 

For example if I select symptoms 1 and 2 I want to display flights 1 and 5 only (with related failure symptoms) but not 2 and 3.

 

I am able to select and display the wanted flights:

List of flights with all selected symptoms = CONCATENATEX(FILTER(ALLSELECTED('Failure Recording'[Flight Number]);[Contains all selected symptoms]=1);'Failure Recording'[Flight Number];"; ")

But I couldn't find a way to reuse this list in a filter.

 

Any suggestion please ?

1 ACCEPTED SOLUTION

Hi @v-juanli-msft 

Based on your answer I've been able to find a solution.

1. Create a separate table used by the slicer:

Failure Symptom Slicer Table = VALUES('Failure Symptom'[Failure Symptom])

 2. Measure to count selected symptoms for each flight:

Flight Selected Symptoms Count = CALCULATE(DISTINCTCOUNT('Failure Recording'[Failure Symptom]);FILTER(FILTER(ALLSELECTED('Failure Recording');'Failure Recording'[Flight Number]=MAX('Failure Recording'[Flight Number]));'Failure Recording'[Failure Symptom] IN ALLSELECTED('Failure Symptom Slicer Table'[Failure Symptom])))

3. Measure to count selected symptoms in slicer:

Filter Selected Symptoms Count = COUNTROWS(ALLSELECTED('Failure Symptom Slicer Table'[Failure Symptom]))

4. Measure to select flights having all selected symptoms:

Contains all selected symptoms = IF([Flight Selected Symptoms Count]=[Filter Selected Symptoms Count]; 1; 0)

 Here are a couple of results:

Selected symptoms 1 & 2Selected symptoms 1 & 2Selected symptoms 1 & 5Selected symptoms 1 & 5

Working sample file available here

Regards,

Dominique

View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

@dom_oab  what is the logic that you don't want to show flight 2 and 3 but only 1 and 5



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Flights 1 and 5 have both selected symptoms 1 and 2.

Flight 2 has symptom 1 but not 2.

Flight 3 has symptom 2 but not 1.

 

@dom_oab  hope attached is what you are looking for



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks a lot @parry2k for your quick answer but this si not exactly what I'm looking for 😞

 

I was already able to list the wanted flights:

ss1.png

The concern is when I try to reuse this list to get all the failure syptoms registered on these flights:

ss2.png

This is the result I'm looking for (all failure syptoms for flights having all selected symptoms):

ss3.png

Any suggestion please?

 

Hi @dom_oab 

1.Create a new table,

slicer table = VALUES(Table1[Failure Symptom])

add column "Failure Symptom" from this table to the slicer, 

Note don't connect this table to original table

 

2.create measures in your table

selected symptom = 
CONCATENATEX(ALLSELECTED('slicer table'),'slicer table'[Failure Symptom],",")

symptom per number = 
CONCATENATEX(FILTER(ALL(Table1),Table1[Flight Number]=MAX(Table1[Flight Number])),Table1[Failure Symptom],",")

if find = SEARCH([selected symptom],[symptom per number],1,0)

12.png

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-juanli-msft ,

 

Thanks four help.

 

Unfortunately your solution works only when the selected symptoms are concatenated in measure symptom per number  one after the other and in the same order than measure selected symptoms.

 

For example if I select symptoms 1 and 5 measure if find should be 1 for flight #5. But since in this case symptom per number is "Symptom1,Symptom 2,Symptom 5" the string "Symptom 1,Symptom 5" is not matched :

if find ko2.png

Another suggestion please ?

 

Regards,

 

Dominique

Hi @v-juanli-msft 

Based on your answer I've been able to find a solution.

1. Create a separate table used by the slicer:

Failure Symptom Slicer Table = VALUES('Failure Symptom'[Failure Symptom])

 2. Measure to count selected symptoms for each flight:

Flight Selected Symptoms Count = CALCULATE(DISTINCTCOUNT('Failure Recording'[Failure Symptom]);FILTER(FILTER(ALLSELECTED('Failure Recording');'Failure Recording'[Flight Number]=MAX('Failure Recording'[Flight Number]));'Failure Recording'[Failure Symptom] IN ALLSELECTED('Failure Symptom Slicer Table'[Failure Symptom])))

3. Measure to count selected symptoms in slicer:

Filter Selected Symptoms Count = COUNTROWS(ALLSELECTED('Failure Symptom Slicer Table'[Failure Symptom]))

4. Measure to select flights having all selected symptoms:

Contains all selected symptoms = IF([Flight Selected Symptoms Count]=[Filter Selected Symptoms Count]; 1; 0)

 Here are a couple of results:

Selected symptoms 1 & 2Selected symptoms 1 & 2Selected symptoms 1 & 5Selected symptoms 1 & 5

Working sample file available here

Regards,

Dominique

dom_oab
Advocate II
Advocate II

Sample report available here

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.