cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
vanessafvg
Super User
Super User

Cummulative values issue when using inactive relationships

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])))





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




5 REPLIES 5
mattbrice
Solution Sage
Solution Sage

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.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yuezhe-msft
Microsoft
Microsoft

Hi @vanessafvg,

Could you please share sample data of your table and post expected result here?

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vanessafvg
Super User
Super User

any ideas on this?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors