How to get Unmatched Row Count between related tables with different filters on each table.

[lakshmanan]

Hi , 
I am new to PowerBI . Need help to write DAX query to get Unmatched counts .
Table 1:

CustomerID	Applied Discount on Product	Product	Discount	Data from Date
1123	Computers-10%	Computers	10%	10/1/2022
1123	Computers-20%	Computers	20%	10/1/2022
43123	Mobile-5%	Mobile	5%	10/1/2022
45434	Clothing-2%	Clothing	2%	10/1/2022
43123	Mobile-15%	Mobile	15%	10/1/2022
2345	Sports Item - 20%	Sports Item	20%	10/1/2022
4546	Fashion - 2%	Fashion	2%	10/1/2022
1243	Misc-24%	Misc	24%	10/1/2022
1123	Computers-25%	Computers	25%	10/4/2022
45434	Clothing-3%	Clothing	3%	10/4/2022
43123	Mobile-25%	Mobile	25%	10/4/2022
5475	Health-23%	Health	23%	10/4/2022
4546	Fashion - 4%	Fashion	4%	10/4/2022
45432	Mobile - 23%	Mobile	23%	10/4/2022
3413	Sports Item - 10%	Sports Item	10%	10/4/2022
3425	Health-32%	Health	32%	10/4/2022
23213	Mobile-6%	Mobile	6%	10/4/2022
2545	Health-43%	Health	43%	10/4/2022
2346	Misc-23%	Misc	23%	10/4/2022

Table 2:

Two Tables are related on "Applied Discount on Product"
Applied Discount on Product  = Combined Text of Product and Discount

Note:Cannot use Merge and leftJoin, due to impact on other requirement.

Users are given Slicers for "Data from Date" and "Shipping Days"

Default Filters handled in DAX:  Customer data should exclude Product "Misc" and shipping data should exclude Product "others"  .

CustomerCount = CALCULATE(DISTINCTCOUNT('Table 1'[CustomerID]),FILTER('Table 1','Table 1'[Product] <> "Misc"))

ShippingCount = CALCULATE(DISTINCTCOUNT('Table 2'[Shipping ID]),FILTER('Table 2','Table 2'[Product] <> "Others"))

When i try to get the Unmatched count of Customer ID with Filters
 i have blocked interaction between Shipping"Data from Date" slicer and result table.
Customer"Applied discount on product" matchs with the Shipping"Applied discount on product" on Old data on shipping table.
Example: User selected Customer"Data from Date" =10/4/2022 , Shipping"Data from Date" =10/11/2022"
Customer:

45432

Mobile - 23%

Mobile

23%

10/4/2022

matchs with shipping on date 10/5/2022 and returns as "matched" . But its originally unmatched since user selected 10/11/2022 date in shipping . 

If i allow the interaction between Shipping"Data from Date" slicer and result table. CustomerId's are excluded in result table.

Please help to me create a DAX to get unmatched Count of Customer ID and 
Count of Shipping ID based on user selection mutiple slicers.

[lakshmanan · Accepted solution]

Issue has been resolved as below,

MisMatch1 = CALCULATE(COUNTROWS(FILTER('Table 1',NOT(CONTAINS('Table 2','Table 2'[Applied Discount on Product],'Table 1'[Applied Discount on Product])))),'Table 1'[Product]<>"Misc")

MisMatch2 = CALCULATE(COUNTROWS(FILTER('Table 2',NOT(CONTAINS('Table 1','Table 1'[Applied Discount on Product],'Table 2'[Applied Discount on Product])))),'Table 2'[Product]<>"Misc")

Also used Selectedcoumns to get the filtered column values from table 2 and matched against table1 using "not contains" to get the result.

[v-jianboli-msft]

Hi @lakshmanan ,

Please try:

First create a new table and apply it to the slicer to replace the slicer "Data from Date Table 2":

Then create a measure and apply it to the filter:

Measure =
IF (
    ISFILTERED ( 'Data from Date Table 2'[Data from Date Table 2] ),
    IF (
        MAX ( 'Table 2'[Data from Date] )
            = SELECTEDVALUE ( 'Data from Date Table 2'[Data from Date Table 2] )
            || ISBLANK ( MAX ( 'Table 2'[Data from Date] ) ),
        1
    ),
    1
)

Final output:

Best Regards,

Jianbo Li

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

[lakshmanan]

Hi @v-jianboli-msft ,
i have tried the steps, but totally there are 10 customers excluding "Misc" product , on filtering using measure on visual , only 2 matchs the shipping on 10/11/2022, so i would like to get unmatched count as 8 on customers. 
Also if i use card visual to display unmatched count, i couldnot apply measure filter on Card .
Is it possible to handle this in DAX query to get unmatched counts.