Forum Discussion
Changing slicer logic to AND
Hi pboron,
kudos for a perfectly formulated post, with screen shots, pbix-file and well described desired outcome.
I think you can solve this by creating a measure which is equal to 1 for the IDs in 'Table' which corresponds to the selected values in the slicer with AND-logic. First delete the relationship between Table2 and Table3. Then create this measure:Filter Measure = IF ( ISFILTERED ( Table3[Criteria] ); COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( Table2[ID] ); "numberOfConcurrentCriterias"; COUNTROWS ( CALCULATETABLE ( FILTER ( Table2; [Criteria] IN VALUES ( Table3[Criteria] ) ) ) ) ); [numberOfConcurrentCriterias] = DISTINCTCOUNT ( Table3[Criteria] ) ) ); 1 )Then add this measure to the Filter pane of your visual and set the value to be equal to 1. Example pbix
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Hi pboron,
kudos for a perfectly formulated post, with screen shots, pbix-file and well described desired outcome.
I think you can solve this by creating a measure which is equal to 1 for the IDs in 'Table' which corresponds to the selected values in the slicer with AND-logic. First delete the relationship between Table2 and Table3. Then create this measure:
Filter Measure =
IF (
ISFILTERED ( Table3[Criteria] );
COUNTROWS (
FILTER (
ADDCOLUMNS (
VALUES ( Table2[ID] );
"numberOfConcurrentCriterias"; COUNTROWS (
CALCULATETABLE ( FILTER ( Table2; [Criteria] IN VALUES ( Table3[Criteria] ) ) )
)
);
[numberOfConcurrentCriterias] = DISTINCTCOUNT ( Table3[Criteria] )
)
);
1
)
Then add this measure to the Filter pane of your visual and set the value to be equal to 1. Example pbix
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Thank you so much, sturlaws. It works exactly as I wanted it. But I do have another question now. Besides matrix I also have a pie chart, that shows 3 measures (basically just counting how many red, yellow and green icons are in the matrix). And I can add measure to filter of pie chart, but can't edit it (can't click anything, so it's always Filter Measure is (All), which means that pie chart always stays the same, ignoring the slicer). Is this intended by Power BI? If so, is there a way, so that this slicer would work with both matrix and pie chart?
Here's your pbix file updated with pie chart if needed.
Strange. Have not seen that before. If I add Table[ID] to the Legend-field of the Pie chart, I'm allowed to change the filtering of e.g. [Filter Measure]. It might be a bug, or it could be by design.
One work-around could be to change your measures to something like this:
Green = CALCULATE ( COUNTROWS ( 'Table' ); FILTER ( 'Table'; 'Table'[Result 1] = 1 && [Filter Measure] = 1 ) ) + CALCULATE ( COUNTROWS ( 'Table' ); FILTER ( 'Table'; 'Table'[Result 2] = 1 && [Filter Measure] = 1 ) ) + CALCULATE ( COUNTROWS ( 'Table' ); FILTER ( 'Table'; 'Table'[Result 3] = 1 && [Filter Measure] = 1 ) )
HI - I am trying to implement the solution you posted and running into some problems.
I have one slicer and one visual matix. . The slicer contains "features" and the matix contains "products" and "features". When i select multiple "features" from the slicer, I would like to apply AND logic so that only the "products" which contain all the "features" selected are displayed.
When no "feature" is selected on the slicer, I'd like all "products" and "features" to be displayed on the matrix.
Both "products" and "features" are from the one same dataset/table.
Thanks in advance.
Hi sturlaws
Kudos for your great solution!
I am working on something similar and I tried to implement this solution, but it didn't work. It's a simple 1 table 1 slicer:
I need to filter only IDs that would match all of the criteria in the slicer so esentially changich the default OR logic to AND. Would you be able to advise please?
Many thanks in advance!
data:
IDRule1 a 2 a 2 b 3 c 3 a 3 d 4 b 4 a 5 b 6 d 6 e 7 d 7 e 7 a 7 f 7 g 8 d 8 a 9 e 10 a 10 d
