Forum Discussion
Helper Icompare columns in two tables, differ the unique values
I'd like to compare two tables:
Table1 contains all IDs
| ID |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
Table 2: records only the IDs which purchase by month
| ID | SalesAmount | Month | ||||
| 3 | 100 | 1 | ||||
| 5 | 200 | 1 | ||||
| 6 | 300 | 1 | ||||
| 12 | 100 | 1 | ||||
| 5 | 700 | 2 | ||||
| 6 | 500 | 2 | ||||
| 10 | 230 | 3 | ||||
| 7 | 100 | 3 |
I'd like to create the visuals as:
A- slicer:
| Month |
| 1 |
| 2 |
| 3 |
If month "2" is marked,
there will be:
- either 2 tables: one with the IDs having sales, another ID with no sale within that month
| ID | SalesAmount |
| 3 | 100 |
| 5 | 200 |
| 6 | 300 |
| 12 | 100 |
| ID |
| 1 |
| 2 |
| 4 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
Or: one completed table:
| ID | SalesAmount | ||
| 1 | 0 | ||
| 2 | 0 | ||
| 3 | 100 | ||
| 4 | 0 | ||
| 5 | 200 | ||
| 6 | 300 | ||
| 7 | 0 | ||
| 8 | 0 | ||
| 9 | 0 | ||
| 10 | 0 | ||
| 11 | 0 | ||
| 12 | 100 |
I am grateful very much for your insights on how to solve it.
With kind regards,
In your scenario, to achieve your goal, we have to create a "dummy" column to filter the unselected IDs. Please refer to my sample below:
1. Create a calculated column for tagging unselected IDs.
Column = IF(ISFILTERED(IDs[ID]),0,1)
2. Then filter IDs table where Column "is blank".
3. Then just add a slicer in your report.
Regards,
3 Replies
v-sihou-msftMicrosoft Employee
In your scenario, to achieve your goal, we have to create a "dummy" column to filter the unselected IDs. Please refer to my sample below:
1. Create a calculated column for tagging unselected IDs.
Column = IF(ISFILTERED(IDs[ID]),0,1)
2. Then filter IDs table where Column "is blank".
3. Then just add a slicer in your report.
Regards,
BusinessAnalystDear v-sihou-msft,
Many thanks for your solution. I have a follow up question that I would like to ask: is it possible to count the number of IDs in table 1 visual?
Regards,
Hello - I see that you solved the below - can you please help with the below
