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
Tontaube2
Helper IV
Helper IV

AND Logic Instead of OR

Hi,

 

I've 5 companies.

1.jpg

 

Some of them have Klimabilanz...

2.jpg

some have Klimastrategie...

3.jpg

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

1 ACCEPTED 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.

1.png

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.

View solution in original post

6 REPLIES 6
Tontaube2
Helper IV
Helper IV

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.

1.png

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 "

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)" into countable rows...
amitchandak
Super User
Super User

@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))

 

 

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.

Top Solution Authors