Dates and relationships

[Anonymous]

Hi,

I want to count the number of assignments id's based on start date and end date.

My table is like this:

Relationships:

Measures:

Assignments Added by StartDate = CALCULATE(DISTINCTCOUNT(Assignments[AssignmentId]); USERELATIONSHIP(Dates[Date]; Assignments[StartDate]))

Assignments by EndDate = CALCULATE(DISTINCTCOUNT(Assignments[AssignmentId]); USERELATIONSHIP(Dates[Date]; Assignments[EndDate]))

But I get the same result on both measures? What am I missing?

[v-frfei-msft]

Hi @Anonymous ,

If you put your measures in card visual and you did not filter them. Then the results should be same. You can try to put the two measures in table visual toget with then EngagementType column, then you will get excepted result.

[Anonymous]

hi @v-frfei-msft 

Ok, but this is a dashboard and I sort of need to use the visual card. I use two different measures and two differet cards, so it should work?

The thing I'm wondering about is how to ask for start and end date, cause I get the same result on both.

---

@v-frfei-msft wrote:

Hi @Anonymous ,

If you put your measures in card visual and you did not filter them. Then the results should be same. You can try to put the two measures in table visual toget with then EngagementType column, then you will get excepted result.

---

[tex628 · Accepted solution]

Your measures are currently calculating the total number of assignments that has a specific startdate/enddate. If you place them both in cards without applying any filters, they will give you the exact same value as every assignment you have will include both start and enddate. 

[Anonymous]

Thanks @tex628 , now I understood 🙂

Do you also know how I can count assignmentsid and filter on  startdate is greater less than today and end date is greater than today?

[tex628]

Do i understand you correctly that you want to count the total number of assignments that are currently active? (Started but not ended)

[Anonymous]

@tex628 Yes 🙂

I was thinking this should work, but it gave me me 113, and it should have been over 200

candicates on assignments = CALCULATE(DISTINCTCOUNT(Assignments[CandidateId]); FILTER(Assignments; Assignments[StartDate]<TODAY()&& Assignments[EndDate]>TODAY()))

Or this one returns 113 as well:

candicates on assignments = CALCULATE(DISTINCTCOUNT(Assignments[CandidateId]);Assignments[StartDate] < TODAY(); Assignments[EndDate] > TODAY())

[tex628]

Those measures look like they should work ... 

Create a normal table, assignment , startdate & enddate then add the measure at the end. You should be able to tell which assignments are included as they should get a "1". 

Find a row that should have a "1" but hasn't and we'll go from there and figure out what's wrong!

/ J

[Anonymous]

Hi,@tex628 

I made the table and found some that should have been one:

I'm suspecting that I need to use "userelationship" to get this to work, and I've been experimenting a bit with it, but haven't figured it out yet. I also tried to replace candidateid with assignment which gave me a bit higher number, but not correct yet.

[tex628]

I used this measure:

AssignmentIDbetween = CALCULATE(DISTINCTCOUNT(Assignments[AssignmentId]),ALL(Dates),Assignments[StartDate]<TODAY(), Assignments[EndDate]>TODAY())

Im getting 422 assignments in total and 225 assignments when you have the filter on Stavanger. 

Could this be correct or is there still something off?

[Anonymous]

hi @tex628 

Correct! I actually just found out myself. It was the slicer on year that was fooling me, and I had to ignore it by using the All(dates)! Thanks a lot!

[tex628]

No worries! Happy to help 🙂

[Anonymous]

eeh, it's me again @tex628.

I just finalized everything and updated the data, seems like I'm of by 3.  I checked in my data and there I have 228

So I tried to debug a bit more by making a table, and I included "date" there. And there I find some blank entries on the date. Can that be the issue? How do I pick them up? Look at the top one here:

[Anonymous]

Ah nevermind. 

I adjusted the calculation to this and it worked 🙂

AssignmentID_all = CALCULATE(DISTINCTCOUNT(Assignments[AssignmentId]);Assignments[StartDate]<=TODAY(); Assignments[EndDate]>=TODAY();ALL(Dates[year]))

[tex628]

If you can, send me a pbix copy in a PM. I can't really figure it out without getting the whole picture

[Mariusz]

Hi @Anonymous 

Can you share a pbix sample file?

Best Regards,
Mariusz

Please feel free to connect with me.

[Anonymous]

Hi @Mariusz ,

Sorry for my late reply. Can I share it as a private message?