Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have the following table:
Flight Number | Failure Symptom |
1 | Symptom 1 |
1 | Symptom 2 |
1 | Symptom 3 |
2 | Symptom 1 |
2 | Symptom 3 |
3 | Symptom 2 |
3 | Symptom 4 |
4 | Symptom 3 |
4 | Symptom 4 |
5 | Symptom 1 |
5 | Symptom 2 |
5 | Symptom 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 ?
Solved! Go to Solution.
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:
Working sample file available here
Regards,
Dominique
@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:
The concern is when I try to reuse this list to get all the failure syptoms registered on these flights:
This is the result I'm looking for (all failure syptoms for flights having all selected symptoms):
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)
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 :
Another suggestion please ?
Regards,
Dominique
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:
Working sample file available here
Regards,
Dominique
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |