FIrst post. New to Power BI. Tried to find answer in forum and couldn’t find answer. Watched countless videos. Still confused. PBIX file attached. Appreciate any advice. Thanks.
Attempted to used COUNTROWS and EXCEPT to find missing employees from the fact table. Success with this formula.
Employees without any reviews = COUNTROWS(
EXCEPT( VALUES( TBI_Person[Identity No] ), VALUES( reviews[Identity No]) ))
Next, need to figure out how to display missing employees by location. Unsuccessful. See table on the first tab of PBIX. Totals as expected. Rows not expected.
I’m sure this is because of my lacking of understanding of row context or data models. Trying to learn but hard to break 20+ years of excel mindset.
Question:
How do I display missing employees (lookup table) from the fact table by a location (lookup table)? ExceptionQuestion.pbix (562.3 KB)
Welcome to the forum – great to have you here! So the good news is that you were off to a terrific start – your data model was solid and you were on the right track. The bad news is that the question you are asking just happened to be a very difficult one that hurled you into the very deep end of the DAX pool. I have the measure working below per your requirements:
Employees without any reviews =
VAR NoReviews =
EXCEPT( VALUES( TBI_Person[Identity No] ), VALUES( reviews[Identity No] ) )
VAR NoRevWithLocat =
SELECTCOLUMNS(
NATURALLEFTOUTERJOIN( NoReviews, 'TBI_Person' ),
"@ID", 'TBI_Person'[Identity No],
"@JobLoc", 'TBI_Person'[Job Location] & ""
)
VAR LocatLineageBreak =
SELECTCOLUMNS(
'locations',
"@JobLoc", 'locations'[Job Location] & "",
"@Geography", 'locations'[Geography]
)
VAR JoinLocatLineageBreak =
NATURALLEFTOUTERJOIN( NoRevWithLocat, LocatLineageBreak )
VAR SelGeog =
MAX( 'locations'[Geography] )
VAR RawResult =
COUNTROWS( FILTER( JoinLocatLineageBreak, [@Geography] = SelGeog ) )
VAR Result =
IF( HASONEVALUE( locations[Geography] ), RawResult,
SUMX( ADDCOLUMNS( JoinLocatLineageBreak, "@ResultCount", RawResult ), [@ResultCount]
)
)
RETURN Result
Here’s what’s going on in the measure above:
the NoReviews table variable is your correctly framed EXCEPT statement
the NoRevWithLocat table variable is doing a couple of things at once – joining the table above to the TBI_Persons table to pick up the job location variable, filtering down to only the specific columns needed, and breaking the lineage on the location column to allow a future outer join
the LocatLineageBreak table variable is pulling the relevant columns from the locations table, and breaking the lineage on job location as well
the JoinLocatLineageBreak table variable is performing a left outer join on the NoRevWithLocat and LocatLineageBreak table variables. Here’s what that intermediate table looks like:
RawResult is counting the rows of the table above for each geography value in the visual
Result is applying a standard pattern to fix the total of the RawResult table
I hope that makes some sense and is helpful. Please feel free to give a shout if you have questions.
@Melissa and I were just talking about this one – this outer join with lineage break pattern hasn’t come up in months, and then just this week it’s come up three times. Full moon or something…
– Brian
P.S.thanks for the UX article you posted – that was fantastic.
@BrianJ Since dataset is really small, can’t really say which one will be fast or slow, but considering DAX isn’t cost based like SQL Server, meaning, for 10 rows, 1000 rows, 10000 rows, 1M rows , 1B rows the engine will probably generate the same query plan, but it could change based on the values iterated. Considering this, this is the order:
Certainly a simpler approach, but in connecting the dimension tables directly don’t you potentially create an ambiguous path whenever that inactive relationship is turned on?
In USERELATIONSHIP, the status of a relationship is not important; that is, whether the relationship is active or not does not affect the usage of the function. Even if the relationship is inactive, it will be used and overrides any other active relationships that might be present in the model but not mentioned in the function arguments.
The function only enables the indicated relationship for the duration of the calculation.
Thank you all for jumping on this. I’m glad to know that this was difficult. Since I am just starting out, I should NOT know how to do this. Ha!
I’m going to practice with each suggestion and learn as I go. Once I understand this and can apply it to real data, this will be so eye opening for my organization. I can’t wait to provide this info.
Thank you very much. I was successful using the Emp w No Review 2. I was able to understand the DAX, plus tweak it so that I could also find the distinct count of employees with and without each type of form that is used (EPE, Check-in, Development).
Perfect, since once the stakeholder finds out that I can tell how many people did not receive any review form. They will then want to find out who received each type form and who did not receive each type of form. Then they will want the % to compare each region against each other.
