Difference (Items) between 2 tables

[Jagan_MFilterIT]

Hi, 

I have a requirement, where we have to find list of values from a columns that are not availalbe in a column in another table. To explain it better, I have a table which has the list of standard products: 

Base Product List

Product Name

Product A

Product B

Product C

Product D

Product E

There is another country level trasaction table which looks likes below: 

Transaction Table
Country	Product Name
Country 1	Product A
Country 1	Product A
Country 1	Product B
Country 1	Product C
Country 1	Product B
Country 1	Product B
Country 1	Product B
Country 1	Product B
Country 1	Product B
Country 2	Product D
Country 2	Product A
Country 2	Product A
Country 2	Product B
Country 2	Product Z

Our requirment is that we have to find of list of products that are not available in transaction table for each country. Similarly we also have to find out if there is any product in the country table that is not available in the product table. Sample Output: 

This needs to be computed dynamically based on dates selected etc. 

Is there any way to do it ? 

[amitchandak]

@Jagan_MFilterIT , have separate country dimesion too. Then plot country from country and product from product and this measure

M1= countrows(Table) 

final measure =

Sumx(Addcolumns( summarize(Table, Product[Product], country[Country]) , "_1", if(isblank([M1]),1 ,blank()) ) , [_1])

[Jagan_MFilterIT]

Hi Sir, 

Thanks for the reply. I tried to recreate this but the final measure is only giving a blank value. I am not able to attach the sample Pbix file. 

[v-kkf-msft · Accepted solution]

Hi @Jagan_MFilterIT ,

Please create a new table:

Crossjoin Table = 
CROSSJOIN (
    VALUES ( 'Transaction Table'[Country] ),
    DISTINCT (
        UNION (
            VALUES ( 'Base Product List'[Product Name] ),
            VALUES ( 'Transaction Table'[Product Name] )
        )
    )
)

Then create measure and filter Measure = 1 for this visual.

Measure = 
var BaseTab = 
    CROSSJOIN (
        VALUES ( 'Transaction Table'[Country] ),
        VALUES ( 'Base Product List'[Product Name] )
    )
var TransTab = 
    GROUPBY ( 
        'Transaction Table',
        'Transaction Table'[Country],
        'Transaction Table'[Product Name]
    )
var InteresectRows = 
    DISTINCT ( 
        UNION ( EXCEPT ( BaseTab, TransTab ), EXCEPT ( TransTab, BaseTab ) )
    )
return COUNTROWS ( INTERSECT ( 'Crossjoin Table', InteresectRows ) )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jagan_MFilterIT]

Sir, Thanks a lot for this! This is exactly what I wanted. I am trying to now understand the logic better! Thanks Much!