cancel
Showing results for 
Search instead for 
Did you mean: 
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 @RicoZhou ,

 

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 @RicoZhou ,

 

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

 

 



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors