Matrix Slicer with OR condition instead of AND

[jwin2424]

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!

[jwin2424 · Accepted solution]

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. 

[vapid128]

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.

[jwin2424]

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!

[daXtreme]

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

[jwin2424]

Maybe I shared the wrong link. What about this?

OR Conidtion Help.pbix

[tamerj1]

Hi @jwin2424 

you can follow

https://youtu.be/l5JX3G5Ntzk

[jwin2424]

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. 

[daXtreme]

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

[jwin2424]

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

[daXtreme]

@jwin2424 

So, is the formula right in your report now?

[jwin2424]

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 😞 

[jwin2424]

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.

[amitchandak]

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

[jwin2424]

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!