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.
I am trying to calculate the net figure of leavers vs joiners over a time.
My primary (active) date relationship is built on the start date
my secondary (inactive) date relationship is built on leaving date
when i get my joiners which is on the primary relationship it works fine, however when i plot my leavers on a graph it is returning 1000 of rows that have no date, it looks like its counting blanks? I dont want it to count blanks. this is very confusing have no idea how to resolve this
CALCULATE(DISTINCTCOUNT('Employees All Time'[Employee No.]),
filter(
all('Date'[Date]),
'Date'[Date] <= MAX('Date'[Date])
not working
"Leavers", CALCULATE(DISTINCTCOUNT('Employees All Time'[Employee No.]),
USERELATIONSHIP('Employees All Time'[Leaving Date],'Date'[Date]),
filter(
all('Date'[Date]),
'Date'[Date] <= MAX('Date'[Date])))
Proud to be a Super User!
Does your 'Date' calendar table include all dates as far back as the earliest date in the 'Employees All Time'[Leaving Date] column?
@mattbrice hi yes it does have all the dates, @v-yuezhe-msft can't share the data but i know what the issue is, its counting the blanks. However I dont want it to count the blanks, how do i ignore them? I tried not(isblank) but that seems to have no affect, however if you recreate the measure where isblank is true then you can tae that new measure and minus the blanks from the original measure, but it seems like a very unelegant way to do it.
Proud to be a Super User!
Hi @vanessafvg,
Where do you put not(isblank) in your formula? Do you use the following formula? You can share table with dummy data.
leavers = CALCULATE(DISTINCTCOUNT('Employees All Time'[Employee No.]),NOT(ISBLANK('Employees All Time'[leaving date])), USERELATIONSHIP('Employees All Time'[leaving date],'Date'[Date]), filter( all('Date'[Date]), 'Date'[Date]<= MAX('Date'[Date])))
Thanks,
Lydia Zhang
Hi @vanessafvg,
Could you please share sample data of your table and post expected result here?
Thanks,
Lydia Zhang
any ideas on this?
Proud to be a Super User!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |