topic Re: AND Logic Instead of OR in DAX Commands and Tips

AND Logic Instead of OR

Anonymous — Mon, 28 Feb 2022 16:09:28 GMT

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

Re: AND Logic Instead of OR

amitchandak — Mon, 28 Feb 2022 16:16:13 GMT

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

Re: AND Logic Instead of OR

Anonymous — Wed, 02 Mar 2022 07:31:02 GMT

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?

Re: AND Logic Instead of OR

Anonymous — Fri, 04 Mar 2022 08:53:18 GMT

Hi Anonymous ,

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.

Re: AND Logic Instead of OR

Anonymous — Mon, 07 Mar 2022 15:25:55 GMT

Hi Anonymous ,

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

Re: AND Logic Instead of OR

Anonymous — Wed, 09 Mar 2022 10:08:31 GMT

Hi Anonymous ,

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.

Re: AND Logic Instead of OR

Anonymous — Mon, 14 Mar 2022 11:04:33 GMT

Hi Anonymous ,

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