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.
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 |
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 |
Filter =
IF (
CONTAINS (
Table1,
Table1[Venue Code], MAX ( Table2[Venue Code] ),
Table1[Customer Code], MAX ( Table2[Customer Code] )
),
1,
0
)
Above is the visual before I use the measure as a filter. So what I will do now is drag the measure into the filter pane and put it as "is 0" in order to show the venue that has a difference. In this case I am expecting Laguna to show up after I apply the filter since there is obviously a difference between the two tables.
But after I apply the filter it shows up as blank like this.
I think it has something to do with the blank rows in Customer Code in Table2 because after putting in "null" instead of leaving those rows empty it actually showed up in the visual. Is there any way I can achieve the same thing while keeping those rows empty?
Any help would be greatly appreciated!
Solved! Go to Solution.
I would take a different approach. Use the Model in Power BI for this. It returns this:
I set up a model using the Venue codes from both tables:
That lets me do this:
I have 3 measures:
Table 1 Records = COUNTROWs('table')
Table 2 Records =
CALCULATE(
COUNTROWS('Table 2'),
NOT ISBLANK('Table 2'[Customer Code])
)
Difference = [Table 1 Records] - [Table 2 Records]
The difference measure goes in the filter pane and is not zero.
My file is here. Try to use Power BI the way it is intended - it expects a Star Schema, not a a SQL database where you are running queries on disconnected tables.
Microsoft Guidance on Importance of Star Schema
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI would take a different approach. Use the Model in Power BI for this. It returns this:
I set up a model using the Venue codes from both tables:
That lets me do this:
I have 3 measures:
Table 1 Records = COUNTROWs('table')
Table 2 Records =
CALCULATE(
COUNTROWS('Table 2'),
NOT ISBLANK('Table 2'[Customer Code])
)
Difference = [Table 1 Records] - [Table 2 Records]
The difference measure goes in the filter pane and is not zero.
My file is here. Try to use Power BI the way it is intended - it expects a Star Schema, not a a SQL database where you are running queries on disconnected tables.
Microsoft Guidance on Importance of Star Schema
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI'm still very very new to power bi so I guess I'm susceptible to making mistakes like these. But thank you so much for the very helpful and detailed response!
Glad I could help. That is why I posted the link to the importance of a Star Schema and the details. Keep studying, and post back to the forum with any questions. Hope the rest of your project goes well @Anonymous !
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHey there! Sorry to bother you again but I was trying to use your solution to achieve something else but I was having difficulties so I figured I should ask you again.
I added some new rows to the tables to make it a bit easier to understand.
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 |
I implemented a slicer with your solution which has three options "Difference", "Common", "All". 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.
I'm trying to figure out how to achieve that with a measure that I will put into the card visual. This is what I have so far but it is just not working.
Count =
VAR CurrentStatus = SELECTEDVALUE('Show Options'[Option])
VAR Checker = [Difference] \\this is the measure you provided in your solution
RETURN
SWITCH(
TRUE(),
CurrentStatus = "Difference" , COUNTROWS(FILTER(Table, Checker <> 0 )),
CurrentStatus = "Common" , COUNTROWS(FILTER(Table, Checker == 0)),
CurrentStatus = "All", COUNTROWS(Table)
)
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |