Inclusive filter across multiple tables

[zenisekd]

Hi, 
(simplicity's sake) I have the following tables:
1. Invoice table - ID, Customer ID, Product ID columns, Revenue, QTY
2. Quote table - ID, Customer ID, Product ID columns, Revenue, QTY
3. Customer table - ID, Market columns
4. Product table - ID, Category columns

My client wants to see a report which would report on invoices, that are categorized as "Car parts". It belongs to this category in case a) the Product is of a "Car parts" category or if b) the customer belongs to a "Car parts" market. 

The report would have multiple visuals (not combining in any way invoices and quotes at the same time on one visual).

Now, my issue is, that because I have a sort of "inclusive" filter requirement, I cannot simply use a page filter and say, filter all markets to Car parts and all categories to Car parts, because it would miss those, that do not fall under both definitions. 
It is a pain to calculate measures for everything (Car Parts qty, revenue for invoices and quotes), that would use the calculate with both columns for all the information needed. 
And I do not want to create a new calculated columns that would just say if it fulfills both definitions (if market or category is car parts). 

I was hoping, that somebody could recommend me some simple measure (or two), which could be used in a page/visual level that would get rid of all unwanted lines from invoices and quotes. 

Thanks.

[v-zhangti]

Hi, @zenisekd 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zenisekd]

Link for the example file

[v-zhangti · Accepted solution]

Hi, @zenisekd 

You can try the following methods.

Measure =
IF (
    OR (
        SELECTEDVALUE ( Customer[Market] ) = "Car Parts",
        SELECTEDVALUE ( 'Product'[Category] ) = "Car Parts"
    ),
    1,
    0
)

Then put the Measure into the Filters on the view and set it equal to 1.

Result:

Measure:

Invoiced QTY = CALCULATE(SUM(Invoices[QTY]),FILTER(ALL(Invoices),[Measure]=1))

Quoted QTY = CALCULATE(SUM(Quotes[QTY]),FILTER(ALL(Quotes),[Measure]=1))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.