Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I've 5 companies.
Some of them have Klimabilanz...
some have Klimastrategie...
some have both.
I've set up my visual according to:
https://medium.com/seismic-data-science/changing-or-to-and-logic-for-power-bi-slicers-1a6b20aee5f5,
to turn OR logic to AND logic.
But I must have made a mistake - it still does not work.
My Measures:
A) MMultifilter = IF(ISFILTERED(FaktMultifilter[Basisinfo:VU]), COUNTROWS(ALLSELECTED(FaktMultifilter[Basisinfo:VU])), 0)
B) SlicerCheck = IF([MMultifilter] = 0, 1, IF(DISTINCTCOUNT(FaktMultifilter[Basisinfo:VU]) = [MMultifilter], 1, 0))
C) Count = CALCULATE(COUNTROWS(FaktStruktur), FILTER(FaktStruktur, [SlicerCheck] = 1))
And visual filter: show only if SlicerCheck = 1
Solved! Go to Solution.
Hi @Tontaube2 ,
According to your statement, I know that when you select both "Ausschlüsse" and "Klimastrategie", you want Grab, Barmenia, Blirk and Blarg are listed- 4 companies which are in both filter you select. I think Power BI will give you a result in "Or" logic when you filter by relationship. Here I suggest you to remove the relationship and create a measure to filter your visual.
Measure:
Measure =
VAR _Filter = VALUES(FaktMultifilter[Multifilter])
VAR _COUNT = COUNTX(_Filter,[Multifilter])
VAR _SELECTBasisinof1 = VALUES(FaktMultifilter[Basisinfo:VU])
VAR _SUMMARIZE = SUMMARIZE(FaktMultifilter,FaktMultifilter[Basisinfo:VU],"COUNT",CALCULATE(COUNTROWS(FaktMultifilter),FILTER(FaktMultifilter,FaktMultifilter[Multifilter] IN _Filter))+0)
VAR _SELECTBasisinof2 = SUMMARIZE(FILTER(_SUMMARIZE,[COUNT] = 2),[Basisinfo:VU])
RETURN
IF(ISFILTERED(FaktMultifilter[Multifilter]),IF(_COUNT =1,IF(MAX(FaktStruktur[Basisinfo:VU]) IN _SELECTBasisinof1,1,0),IF(MAX(FaktStruktur[Basisinfo:VU]) IN _SELECTBasisinof2,1,0)),1)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @amitchandak ,
I already have it as a visual level filter.
One problem is: The companies are on rows, and I cannot drag a measure on rows in a matrix visual.
Maybe I've made another error - in understanding.
I want to show companies which have e.g. Klimabilanz AND Klimastrategie. This column of companies to show is FaktStruktur[Basisinfo:VU]. They are filtered by FaktMultifilter (...I misnamed the DIM). Here their key is FaktMultifilter[Basisinfo:VU].
What's my mistake?
Hi @Tontaube2 ,
From your screenshot above , we couldn't see matrix.
As far as I know, Power BI doesn't support us to add a measure into matrix column or matrix row. If you want to filter matrix column or row dynamiclly, I think create a measure , add it into visual level filter and set it to show items when value =1 is a good idea. If you are still confused about this problem, please share a sample file with us. And you can show us a screenshot with the result you want. This will make it easier for us to find the solution.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-rzhou-msft ,
here is the link to the sample file: https://www.dropbox.com/s/36i1k23jukf14ht/Test.pbix?dl=0
If I select "Klimastrategie", Grab, Barmenia, Blirk and Blarg are listed- 4 companies.
If I select "Ausschlüsse", Grab, Knurz, Barmenia, Blirk and Blarg are listed - 5 companies.
If I select both "Ausschlüsse" and "Klimastrategie", all 5 companies are listed ("OR"-logic) instead of only the intersect/ "AND"-logic - which I want.
The companies are on rows: Basisinfo:VU
It's a matrix instead of "simple" data bars. I've switched on: Show data bars.
I need a matrix visual because I display additional information for each company.
Right now, the Measure "Counter" (MCountVUs) isn't applied as visual filter - because right now it "breaks" everything!
Measure "MSlicerCheck" and Measure "MMultifilter" are according to https://medium.com/seismic-data-science/changing-or-to-and-logic-for-power-bi-slicers-1a6b20aee5f5,
Thanks for your help!
Bye
Michael
Hi @Tontaube2 ,
According to your statement, I know that when you select both "Ausschlüsse" and "Klimastrategie", you want Grab, Barmenia, Blirk and Blarg are listed- 4 companies which are in both filter you select. I think Power BI will give you a result in "Or" logic when you filter by relationship. Here I suggest you to remove the relationship and create a measure to filter your visual.
Measure:
Measure =
VAR _Filter = VALUES(FaktMultifilter[Multifilter])
VAR _COUNT = COUNTX(_Filter,[Multifilter])
VAR _SELECTBasisinof1 = VALUES(FaktMultifilter[Basisinfo:VU])
VAR _SUMMARIZE = SUMMARIZE(FaktMultifilter,FaktMultifilter[Basisinfo:VU],"COUNT",CALCULATE(COUNTROWS(FaktMultifilter),FILTER(FaktMultifilter,FaktMultifilter[Multifilter] IN _Filter))+0)
VAR _SELECTBasisinof2 = SUMMARIZE(FILTER(_SUMMARIZE,[COUNT] = 2),[Basisinfo:VU])
RETURN
IF(ISFILTERED(FaktMultifilter[Multifilter]),IF(_COUNT =1,IF(MAX(FaktStruktur[Basisinfo:VU]) IN _SELECTBasisinof1,1,0),IF(MAX(FaktStruktur[Basisinfo:VU]) IN _SELECTBasisinof2,1,0)),1)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-rzhou-msft ,
many thanks!
One last question:
How do I "count" the returned rows? It seems I cannot count a measure, and I am too much of a newbie to decipher the Measure "
@Tontaube2 , to make this to work
FILTER(FaktStruktur, [SlicerCheck] = 1))
either use this as visual levelfilter [SlicerCheck] = 1
or use group by viusal
CALCULATE(COUNTROWS(FaktStruktur),FILTER(values(FaktStruktur[Axis Column]), [SlicerCheck] = 1))
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |