Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So I have two tables which I want to compare using a slicer which has the options of "Difference", "Common", "All"
Table1
Venue Code | Venue Name | Customer Code |
Laguna | Suria | 101Suria |
Laguna | Suria | 102Suria |
Laguna | Suria | 103Suria |
Laguna | Suria | 104Suria |
Laguna | Suria | GENSURIA |
Laguna | Suria | GENSURIA |
Laguna | Suria | 105Suria |
Laguna | Suria | 106Suria |
Laguna | Suria | 107Suria |
Bahamas | Sunway | Bah123 |
Bahamas | Sunway | Bah124 |
Bahamas | Sunway | Bah125 |
Table2
Venue Code | Venue Name | Customer Code |
Laguna | Suria | 101Suria |
Laguna | Suria | 102Suria |
Laguna | Suria | 103Suria |
Laguna | Suria | 104Suria |
Laguna | Suria | GENSURIA |
Laguna | Suria | GENSURIA |
Laguna | Suria | |
Laguna | Suria | |
Laguna | Suria | |
Bahamas | Sunway | Bah123 |
Bahamas | Sunway | Bah124 |
Bahamas | Sunway | Bah125 |
So now I will have two card visuals which will display the count of customer code for each of the tables. If the slicer is selected at "All" then the card visual for table 1 will show 12 while the card visual for table 2 will show 9. If "Difference" is selected at the slicer then the card visual for table 1 should show 9 while card visual for table 2 will show 6. If "Common" is selected at the slicer then the card visual for table 1 should show only 3 while the card visual for table 2 will only show 3 as well (because Bahamas is the only common one).
Below is the logic I have in mind but I cannot figure out the exact condition I should set.
Count =
VAR CurrentStatus = SELECTEDVALUE('Show Options'[Option])
if CurrentStatus = "Difference" && SOME CONDITION, then count only the rows that meet the condition
else if CurrrentStatus = "Common" && SOME CONDITION, then count only the rows that meet the condition
else if CurrentStatus - "All", then count all the rows
RETURN Whatever has been counted
Any help would be greatly appreciated!
Hello @Anonymous ,
I would use the switch function and then build the slicer as a table.
Meaning:
Create a table where you enter the three different status.
Then create three different measures for each card, counting what you want to count (distinct count, differences and common), so you have 6 measures in total.
Then place the measures on the card visual and fill the slicer with the created table.
Does that help? If so, please
Thanks hashtag_pete
Hi @Anonymous ,
I can understand what you want, but I don't understand the logic of returning the results.
When the slicer selects 'ALL', why does Table 2 return 9 instead of 12?
Why does the slicer select 'Difference', the result of Table 1 is 9 and the result of Table 2 is 6?
Please tell me your counting logic in detail.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I would be basing the Customer Code count on the Venue Code, so there are two venues which is Laguna and Bahamas. Laguna has 9 customer code count in table1 while it only has 6 customer code count in table2 (3 rows that are not matching), that is why if I select "Difference" only the customer code count of each Laguna from their respective tables will be counted since the Laguna from the two tables have differences, that is why the card visual for table1 should show 9 while the card visual for table2 should show 6. Meanwhile Bahamas in table1 has 3 customer code count and likewise the same in table2 (all 3 rows are matching), that is why it is the only thing that will be chosen when selecting "Common"
There are built-in functions in DAX for this
INTERSECT() - commonalities
EXCEPT() - differences
UNION () and SUMMARIZE() - all
Note: You have duplicate rows in each table. The results of the Venn Diagrams will be skewed. Better to have unique row identifier columns.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |