Creating a relationship on my daily occupancy chart

[FlankyPank2___]

Hi

I've produced a chart plotting daily occupancy (From wonderful support on here!).

The info comes from a spreadsheet called "Data" in a format like:

ID Number    Name                 Programme        Start Date       End Date      Location            

ID-11111       John Smith         Pgrm 1               01/04/2023    03/06/2023   Staffordshire     

ID-11122       Phil McCrackin   Pgrm 2               26/10/2023    04/12/2023   Derbyshire        

ID-11133       Robin Banks       Pgrm 1               25/08/2023    26/09/2023   Leicestershire 

ID-11133       Robin Banks       Pgrm 2               26/09/2023    15/10/2023   Leicestershire

I've inclued a new column inside "Data" called "Column" = 1

I also needed to produce a new date table called "Date23-24" with every single day of the year.

I then added this measure:

Total Value = SUMX(FILTER(Data,Data[Start Date]<=max('Date23-24'[Date])&&Data[End Date]>=max('Date23-24'[Date])), [Column])+0

This plots wonderfully on a chart.  It responds to a date slicer using "Date23-24" and responds to the other selections, such as building 1.

My problem, is, i'd really like the distinct count of the ID Number to change along with my other selections but it doesnt. It currently just provides my with an annual running total .

So if i select Qtr 1 on my date slicer and Staffordshire on my location slicer - the occupancy line on my chart changes accordling - But my distinct ID number count (client count) on the card and treemap doesn't return the Qtr, just that annual total.  I have slicers for prgm, date & location on this chart.

rightly or wrongly i've been trying to modify this measure, provided to me for a different reason throught power bi.

Bed Days Used: Building =

var _select=SELECTEDVALUE('Data'[Building])

var _table=

SELECTCOLUMNS('Date23-24',"Date",[Date])

var _table2=

CROSSJOIN(

    'Data',_table)

return

COUNTX(

   FILTER(

    _table2,[Date]>=[Start Date]&&[Date]<=[End Date]&&[Building]=_select),[Building])

Thanks for your time & patience. I hope my post makes some sort of sense! I want my card (count of distinct client ID) and my treemap (Location) to be response to the date slicer.

FPx

[HotChilli]

Got the pbix, thanks.

You are using a disconnected date table so that you can use Fact data between the start date and end date.

The card won't respond to changes in the date slicer because there is no relationship between the 2 tables.

You'll need to write a measure similar to the Total Value measure for this

---

However, are you happy with the Total Value measure? To me (and I don't know your business requirements) the date comparisons don't look correct.

max('Date23-24'[Date]) is the max value from the date slicer. You don't seem to be using the min value at all.

[FlankyPank2___ · Accepted solution]

Thank you for the reply.

I'm just trying to track/count daily building residency.  Swapping between certain critera.  Using an active spreadsheet that uses a start & end date.  

I followed a youtube tutorial

 https://youtu.be/O653vwLTUzM

which gave me the total Value measure - i'll return to it and check.  Although at the time I double checked against an excel version I have and it seemed to match.

I tried modifying the Total Value measure initially, but failed!

I think i need to concentrate on learning more dax instead of winging it lol.

Thanks 

[v-yohua-msft]

Hi, @FlankyPank2___ 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yongkang Hua

[HotChilli]

I think you've got to the point where we need to see your pbix to investigate this.

Post a pbix with sample data and i'll have a look.

[FlankyPank2___]

https://www.dropbox.com/scl/fi/pqo5ae39nkcgqvwsshqr3/Mock-Occupancy-for-Forum-Use.pbix?rlkey=igz5264...

I hope that works lol.  Sorry it took so long.  

I've kind of changed the data groups on this.  But if i select Amber, the card and treemap reflect the distinct count of ID Numbers but it isn't responsive to the Qtr, Month date slicer.

The same occurs when i select a programme from the treemap.  I'd ideally like the ID number to distinct count responsively with the Date Slicer.

I hope my link works & it all makes sense!

Thanks for your help.

[FlankyPank2___]

ok, thank you. 

My original has sensitive data so i'll create a mock one & share it.  Thanks