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.
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]))
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 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |