The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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]))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
88 | |
71 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |