topic Re: Matrix Slicer with OR condition instead of AND in DAX Commands and Tips

Matrix Slicer with OR condition instead of AND

jwin2424 — Tue, 19 Jul 2022 23:15:33 GMT

Hello,

I have looked all over and can't seem to find what I need. I am looking to use an OR condition with filtering. I have attached a sample file for help.

I have two tables: Sales and Territory. I am trying to get a matrix that shows the sum of revenue based on either the End Customer Name or the Territory.

Here is what I would like:

I would like to be able to see all sales from Ray as well as all sales from Territory C. I found a workaround that almost works. (Applying filters with OR and XOR conditions in Power BI — Apex Insights: Power BI tips & tricks)

The downside to the link is that I MUST select criteria from both slicers. I would like to be able to see Territory C only, Ray only, or both combined.

OR Conidtion Help.pbix

I can't figure it out. Any help would be great!

Re: Matrix Slicer with OR condition instead of AND

amitchandak — Wed, 20 Jul 2022 03:43:22 GMT

jwin2424 , both slicer need to be from independent tables

meausre =

var _tab1 = values(Territory1[Territory])

var _tab2 = values(Customer1[End Customer])

return

calculate(Sum(Table[Value]), filter( Table, Table[Territory] in _tab1 || Table[End Customer] in _tab2) )

Re: Matrix Slicer with OR condition instead of AND

tamerj1 — Wed, 20 Jul 2022 04:40:13 GMT

Hi jwin2424

you can follow

https://youtu.be/l5JX3G5Ntzk

Re: Matrix Slicer with OR condition instead of AND

jwin2424 — Wed, 20 Jul 2022 05:10:50 GMT

I watched his video already. I downloaded his file and noticed something that seems to be an error (Or at least not what I am looking for).

If you select JUST one (Professional), you get a single count of 53

If you select JUST one from the other slicer (Tailspin), you get 375 units.

You should see 53 + 375 (428) but we don't.

It would appear that it is EXCLUDING the AND conditions (There are 24 units that are under the AND condition and 428 - 24 = 404).

I need the total to be 428.

😞

That is a great video though, as are all of his videos.

Re: Matrix Slicer with OR condition instead of AND

jwin2424 — Wed, 20 Jul 2022 05:25:52 GMT

I tried this a few different ways, and I don't get any results.

Territory is a seperate table with a one way relationship to Sales.

Territory already has distinct values.

Just to see if the formula worked, I only used the Sales table and not the Territory table:

The slicers below are directly from the Sales table.

I should be seeing 92, but instead it returns nothing.

I am curious if you are able to try in the pbix file I downloaded.

Thanks!

Re: Matrix Slicer with OR condition instead of AND

daXtreme — Wed, 20 Jul 2022 12:47:56 GMT

jwin2424

If you have 2 sets, A and B, and they do intersect, then to calculate the number of elements in A+B, you have to use this formula: |A| + |B| - |A*B|. If you don't remove the intersection, you'll count some elements twice. So, I think what you get is correct. In your case the number is less than the sum because there are some "elements" that belong to both "categories": Professional and Tailspin Toys.

Re: Matrix Slicer with OR condition instead of AND

daXtreme — Wed, 20 Jul 2022 12:49:27 GMT

By the way.... the file cannot be accessed from outside.

Re: Matrix Slicer with OR condition instead of AND

jwin2424 — Wed, 20 Jul 2022 17:49:14 GMT

Maybe I shared the wrong link. What about this?

OR Conidtion Help.pbix

Re: Matrix Slicer with OR condition instead of AND

jwin2424 — Wed, 20 Jul 2022 18:37:56 GMT

I see what you're saying. It calculates as not to double add the values. That makes sense.

Re: Matrix Slicer with OR condition instead of AND

jwin2424 — Wed, 20 Jul 2022 18:45:35 GMT

I followed the video again and have a few questions:

1: The formula relies on two tables outside of the fact table.

The end customer table for me is bogging down my actual report. I already have the end customer in the fact table (Sales), so why can't I use the values from this table? Can I avoid making a seperate table for this and just use to two fields as shown?

2: This DAX works ONLY when two criteria are selected. Some of the end users of the report only need one of the slicers (End Customer/Territory) and some end users of the report need to use both slicers.

With two slicers in use, I see data in both the AND and OR conidtion

But with one criteria, my measure breaks and doesn't show 64.

Is there a way to put an IF statement that says like "If one slicer is used, return the value. If two slicers are used, use the measure?"

Thanks!

Re: Matrix Slicer with OR condition instead of AND

vapid128 — Thu, 21 Jul 2022 02:56:40 GMT

new table:

R_ID = VALUES(dw_orders[restaurant_id])

YYYYMM = VALUES(dw_orders[YYYYMM])

new Measure

Measure =

IF(

MAX(dw_orders[restaurant_id]) in VALUES(R_ID[restaurant_id]),

1,0

) +

IF(

MAX(dw_orders[YYYYMM]) in VALUES(YYYYMM[YYYYMM]),

1,0

)

filter measure is greater than 0.

Re: Matrix Slicer with OR condition instead of AND

daXtreme — Thu, 21 Jul 2022 08:45:41 GMT

jwin2424

So, is the formula right in your report now?

Re: Matrix Slicer with OR condition instead of AND

jwin2424 — Thu, 21 Jul 2022 17:37:20 GMT

The video worked previously, I just didn't understand that it was excluding double-counting. I reran the formula, but I still have the issue that it only works when two criteria are selected. I would love to select from either one slicer or both slicers and get a correct total. In the video formula, the total is incorrect if just one slicer is selected.

Re: Matrix Slicer with OR condition instead of AND

jwin2424 — Thu, 21 Jul 2022 22:34:17 GMT

I'm so close, but I don't see where I am going wrong in my formula.

In the above formula, I can get a working OR function with the highlighted DAX.

If I select one item, I get the correct result:

When I select two criteria, my standalone formula works correctly, but my IF statement breaks.

I don't know why it won't return the correct answer 😞

Re: Matrix Slicer with OR condition instead of AND

jwin2424 — Thu, 21 Jul 2022 22:52:53 GMT

I figured it out!

OR #2 = VAR Terr = VALUES(Territory[Territory Code (PK)]) VAR EndC = VALUES('End Customer Name'[End Customer]) VAR _ORSum = CALCULATE( sum(Sales[Sales Net Revenue]), CALCULATETABLE( FILTER ( Sales, OR ( RELATED ('End Customer Name'[End Customer]) IN EndC, RELATED (Territory[Territory Code (PK)]) IN Terr ) ), ALL ( Territory[Territory Code (PK)] ), ALL ('End Customer Name'[End Customer]) ) ) RETURN IF( AND( ISFILTERED('End Customer Name'[End Customer]), ISFILTERED(Territory[Territory Code (PK)]) ), _ORSum, sum(Sales[Sales Net Revenue]) )

I am not sure why reversing the IF argument worked, but it did!

And if I select a second criteria:

So there you go.. the more you select, the more you get.