How to filter by a measure comparing 2 tables

[adnan_makie]

Hey Everyone,

I have 2 tables connected via a bridge table in a one <> one relationship, I currntly have this DAX:

Comparison_Category =

IF (

    MAX('Table 1'[quantity_1]) <> MAX('Table 2'[quantity_2]),

    "Quantity Different",

    "Quantity Similar"

)

The DAX is working fine, but when I try to insert this formula in a slicer it doesn't work. And creating it as a calculated column in one of the tables gives me only 1 result (quantity different).

How can I filter by this measure only 1 of the tables , let's say Table 1 in this case?

[Ashish_Mathur]

Hi,

What exactly are you trying to do?  Share some data (in a format that can be pasted in an MS Excel file), explain the question and show the expected result.

[adnan_makie]

Hi Ashish, 

Let's say I want to compare the quantity from table 1 to the one in table 2 , if there is a difference I want the formula to give me a 1 for the id and if there's no difference then it should be a 0. Considering that both tables might (or might not) have the same ids that are duplictaed and these tables would be connected by a simple bridge table in a one to many or one to one relationship. After having the comparison and the formula providing me with a 1 or 0, I would like to filter the id's that have a different quantity in table 1 and 2, in that case, maybe I would want to create a KPI card that will show me the number of ids with a different quantity.

Atm, I'm not able to use a measure in a visual filter neither in a slicer. Kindly find a sample data for your reference below:

id_1	quantity_table_1
1563	1
2345	2
4344	3
9023	6

1563	1
2345	3
4344	3
9023	7

[Ashish_Mathur]

Based on the 2 tables that you have selected, show the expected result.

[adnan_makie]

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas in Table1

Qty1 = calculate(sum('Table2'[Quantity]),filter('Table 2','Table 2'[ID]=earlier('Table 1'[ID])))

Diff = 1*('Table 1'[Qty]-'Table 1'[Qty1])

Write this measure and drag it to a card visual

Measure = calculate(countrows('Table 1'),'Table 1'[Diff]=1)

Hope this helps.

[v-jialongy-msft]

Hi @adnan_makie 

Measures behave differently from columns in Power BI and can't directly be used in slicers the way you might expect. Measures calculate values dynamically based on the current context of the report, whereas slicers require a column to work from.

Best Regards,

Jayleny

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

[adnan_makie]

Is there any workaround you're aware of?