Forum Discussion

Jagan_MFilterIT's avatar
Jagan_MFilterIT
Helper I
4 years ago
Solved

Difference (Items) between 2 tables

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 
  
CountryProduct Name 
Country 1Product A
Country 1Product A
Country 1Product B
Country 1Product C
Country 1Product B
Country 1Product B
Country 1Product B
Country 1Product B
Country 1Product B
Country 2Product D
Country 2Product A
Country 2Product A
Country 2Product B
Country 2Product 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 ? 

 

 

Need Help
Tutorial Requests
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Jagan_MFilterIT
      Helper I
      4 years ago

      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's avatar
        v-kkf-msft
        Community Support
        4 years ago

        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.