Forum Discussion

Matt_Mohawk's avatar
Matt_Mohawk
Helper II
3 years ago

Distinct Job Count based on Start Date

Hi,   I need to get a distinct count of jobs based on Entered Date. This is the day that it was entered into our systems. For some reason, the data that shows is only working based on Start Date fo...
Need Help
Mikelytics's avatar
Mikelytics
Resident Rockstar
3 years ago

Hi Matt_Mohawk 

 

Then I am pretty sure its because of your data model. Do you have an relation between the ENtry column and the date column of your data table? If not then this is the issue. You have to create the connection. As a result it should look like this

the highlighted line is a inactive relationship.

 

Now you have to add in the emasure the USERELATIONSHIP function

Number of jobs =
VAR __MinDate = MIN(DimDate[Date])
RETURN
CALCULATE(
   DISTINCTCOUNT(FactTable[JobNumberColumn]),
   DimDate[Date] >= __MinDate,
   USERELATIONSHIP(DimDate[Date],FactTable[EntryColumn])
)

 

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

 

Matt_Mohawk's avatar
Matt_Mohawk
Helper II
3 years ago

I currently have a relationship built for every table that I currently use. I use the Date Out field for that table because I have other data that I have to display other than what I had provided you. See image below to see the Relationship area.