Forum Discussion

Jagan_MFilterIT's avatar
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 stan...
  • 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.