Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I'm looking for help on a piece of DAX that has had me stumped for a few days now, although it's more than likely very simple. I have a table in my data model like the image below that contains fields EmpNo, LastName, FirstName, StartDate, EndDate, and TenureYears. The TenureYears column is a calculated column based on the age of the StartDate.
I would like to average the TenureYears of only the rows with a blank end date. For example, the correct answer, in this case, would be 5.5. Not 4.2 which is an average of the whole column. Similarly, I would like to create a second measure to calculate the average TenureYears of only the rows with an end date. I have tried a number of expressions but so far haven't managed to find the correct one.
Can anyone point me in the right direction?
A link to the example data set is attached.
https://1drv.ms/x/s!AooV7yMIZkprj2foRPCEW_E7CVes?e=8fzjhe
Solved! Go to Solution.
@Sgeater , try
blank end date
calculate(average(Table[tenure year], isblank(Table[end Date]))
non blank end date
calculate(average(Table[tenure year], not(isblank(Table[end Date]))
@Sgeater , try
blank end date
calculate(average(Table[tenure year], isblank(Table[end Date]))
non blank end date
calculate(average(Table[tenure year], not(isblank(Table[end Date]))
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
104 | |
93 | |
64 | |
61 |