topic Re: Taking set difference of slicer and values from one table to another in DAX Commands and Tips

Taking set difference of slicer and values from one table to another

afaro — Wed, 08 May 2024 14:02:24 GMT

I have a slicer where you can select products - A, B, C, D, E.

Let's say you select products A, B and you get a matrix/table with list of customers and the total sales made to them (non-zero values only). Let's say this is visual 1.

Once I get this matrix/table with customer list, I want another table/matrix which shows me the total sales made to the same customers in visual 1 but with the remaining products not selected in the slicer i.e. for C, D, E.

How can I approach this?

Re: Taking set difference of slicer and values from one table to another

tamerj1 — Wed, 08 May 2024 14:05:27 GMT

Hi afaro
It depends. How many products do you have in your real data? Can you provide a small dammy sample to work with?

Re: Taking set difference of slicer and values from one table to another

Anonymous — Mon, 27 May 2024 06:34:25 GMT

Hi afaro ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create a new table

Table 2 = VALUES('Table'[Prouct])

Create measres

Sales amount by Product = CALCULATE( SUM('Table'[Sales]), ALLEXCEPT( 'Table', 'Table'[Prouct] ) )

Measure 1 = IF( SELECTEDVALUE('Table'[Prouct]) IN VALUES('Table 2'[Prouct]) && [Sales amount by Product] <> 0, 1, 0 )

Measure 2 = IF( SELECTEDVALUE('Table'[Prouct]) IN VALUES('Table 2'[Prouct]), 1, IF( [Sales amount by Product] = 0, 1, 0 ) )

Appply measure 1 to visual 1 and measure 2 to visaul 2

Fianl output

Best regards,
Albert He

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