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.
Hello everyone,
I've been having trouble finding a solution for this problem, having not been able to replicate solutions posted to similar problems featured on the forums.
In the link below, you'll be able to download my dummy dataset.
https://my.syncplicity.com/share/dbtnt5bxh0stupg/Dummy%20Data
Essentially, I have a client table that lists a given client's "Client ID" , the "Program" the client entered, the corresponding dates of "Admission" and "Discharge" into said program, etc. Each row is therefore a record of client admissions into a given program. All clients are assigned a unique Client ID, that's used across all programs these clients happen to enter. Clients will appear in many programs, multiple times across different time periods. Hence, Client IDs will appear multiple times throughout the Client Table.
Here are the columns:
Client ID | Program | Admission Date / Project Start Date | Discharge Date | Client Status | RS StatuS |
Ultimately, I want the Client Table, based on both the Admission and Discharge Dates selected, to show the latest record for each Client ID. I don't care about what program the client was in, but rather what was the lastest record. Therefore, the Client Table should de-duplicate based on "Client ID", given the Admission and Discharge Dates selected. In addition to the Admission and Discharge Date columns being used as slicers, "Client Status" and "RS Status" will also be made into slicers, therefore de-duplication should take these into account as well.
How I'd like for all this to work is through DAX calculated columns. I want a calculated column (something like a "Latest Record - Yes or No") to run through the table, determining whether a given row counts as the latest record for that Client ID, given the admission/discharge date slicers selected, along with whatever slicer value selected on the "Client Status" and "RS Status" slicers.
The tricky part is that I want the Admission and Discharge Date slicers to show clients who were "in program" during the date range created when selecting an individual Admission and Discharge Date. So for example, if I selected an Admission Date of 01/01/2017 and a Discharge Date of 12/31/2017, a client record that has both an admission date of 09/01/2016 and a discharge date of 03/03/2017 should still remain on the Client Table since this record was active during the 01/01/2017-12/31/2017 time period selected. Given this rule and any other slicer values selected, I want the Latest Record calculated column to determine which rows should count as a client's latest record. Lastly, the Latest Record calculated column should be capable of being used as a slicer as well.
Hi @SDream7
To show the latest record for each Client ID based on both the Admission and Discharge Dates selected, you could create these measures.
(i can't find any rows with both lastest Admission and Discharge Dates as the slicer show.
based on the slicer, lastest Admission and Discharge Dates are 2017/6/14 and 2018/5/15, but there isn't any row with 2017/6/14 and 2018/5/15 for Admission and Discharge Dates meanwhile
So i assume show the lastest Admission date based on the lastest Discharge Dates, this is to mean for the lastest Discharge Dates 2018/5/15, show the lastest Admission date 2017/5/28)
max of Admission Date = CALCULATE(MAX([Admission Date / Project Start Date]),ALLSELECTED(Sheet1)) max of Discharge Date = CALCULATE(MAX([Discharge Date]),ALLSELECTED(Sheet1)) based on last discharge daate = CALCULATE(MAX([Admission Date / Project Start Date]),FILTER(ALLSELECTED(Sheet1),[Discharge Date]=[max of Discharge Date])) last record = IF(MAX([Admission Date / Project Start Date])=[based on last discharge daate]&&MAX([Discharge Date])=[max of Discharge Date],1,0)
Best Regards
Maggie
Hi @SDream7
I want the Client Table, based on both the Admission and Discharge Dates selected, to show the latest record for each Client ID
this can be done by creating a measure instead of a calculated column for calculated columns can't change with the slicer.
"the Latest Record calculated column should be capable of being used as a slicer as well"
To make the Latest Record change based on the slicer, it should be a measure, so it can't be used as a slicer.
I believe the requirements from your statements can be achieved except creating the Latest Record as a calculated column.
Best Regards
maggie
Thank you for your response. How might I go about creating what I want into measures?
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 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |