Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi - I have a dataset that provides historic values for each ID. So, the status for ID 574142 went from 0601 on September 13th to 0803 to September 16th.
My goal is to have the count of each status by day in a matrix. Using the same ID as an example, the matrix would count that ID as 0601 on the 13th, 14th and 15, and then would start counting under the 0803 status as of the 16th, until the ID shows up with a different status again.
Is there either a measure that I can write in DAX, or something that I can do in Power Query that would help with this? I appreciate the help!
I created an example file here: https://drive.google.com/file/d/1IxatzxiXvVAx3WNWcS0LRb4L3sTwLEXo/view?usp=drive_link
Solved! Go to Solution.
Hi @renlaforest ,
I suggest you to disconnect the relationship between two tables to use the continuous date.
Measure:
Count in continuous date =
VAR _GENERATE =
GENERATE ( 'Calendar', VALUES ( Query1[B005501HOME_NO] ) )
VAR _ADD1 =
ADDCOLUMNS (
_GENERATE,
"LASTDATE",
CALCULATE (
MAX ( Query1[CAST(TO_TIMESTAMP(B0055TIMESTAMP,'YYYY-MM-DDHH24:MI:SS.FF1')ASDATE)] ),
FILTER (
Query1,
Query1[B005501HOME_NO] = EARLIER ( [B005501HOME_NO] )
&& Query1[CAST(TO_TIMESTAMP(B0055TIMESTAMP,'YYYY-MM-DDHH24:MI:SS.FF1')ASDATE)]
<= EARLIER ( [Date] )
)
)
)
VAR _ADD2 =
ADDCOLUMNS (
_ADD1,
"STATUS",
CALCULATE (
MAX ( Query1[PPM_STATUS] ),
FILTER (
Query1,
Query1[B005501HOME_NO] = EARLIER ( [B005501HOME_NO] )
&& Query1[CAST(TO_TIMESTAMP(B0055TIMESTAMP,'YYYY-MM-DDHH24:MI:SS.FF1')ASDATE)]
= EARLIER ( [LASTDATE] )
)
)
)
RETURN
COUNTX (
FILTER (
_ADD2,
[Date] = MAX ( 'Calendar'[Date] )
&& [STATUS] = MAX ( Query1[PPM_STATUS] )
),
[B005501HOME_NO]
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the response. Maybe I should have been clearer with what I need. I basically need the count of current status (at the time). So, for these two homes in the example file, it would end up looking like this:
Thank you @lbendlin - I'm not sure how the data doesn't match the expected result. Both start out as 0601 on September 13th, then are 0803 on September 16th, and one of them, 5750263, switches to 0805 on September 27th, while 5754142 remains 0803 until October 20th (when it switches to 0402 and stays there).
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |