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.
Hey, thanks for the help.
I have this table:
one action_id can have multiple ds_action_sk. On the other hand, each ds_action_sk canhave multiple status_id .
I need to create a DAX measure in order to visualize the change of status by insertion_date:
So the first time action_id = 2455617 appeared was on 11/5/2022 (We have a 1 on the 11/5/2022 representing the count of action_id. Then it appears again on 13/5/2022 but it has the same status_id = 0 ( Noticed in the table that we still have the count '1' on 13/5, as there is only one action_id (2455617 ) with status '0' , it doesn't take into account the ds_action_sk )
BUT on 14/5/2022 the action_id = 2455617 changes to '1', then in the table we see that the status 0 now has 0 counts and the status 1 starts to have a 1 count, because there is an action_id with status 1.
Summarizing, I need a measure to distinct count the action_id depending on the status. Then if the status changes, the count need to reflect 0.
Thank again.
PBI FILE: https://drive.google.com/drive/folders/1Rd_JzzeRm_T-ZMoS_J8a3BXCVKq1N2LN?usp=sharing
Solved! Go to Solution.
Use status_id from dimension instead of the fact_table in the table visual.
And in the measure replace this:
VAR _status =
CALCULATE ( SELECTEDVALUE ( 'fact_ds_action'[status_id] )
with this:
VAR _status =
CALCULATE ( SELECTEDVALUE ( 'dim_ds_status'[status_id] ) )
Hi @sturlaws , thanks for sharing this. The calculated column is working but not the measure. Please, let me attached a pbix with the sample data and the column/measure you've created. Hope this brings clarity to my requirement. Also there is a complete_date that only has the action_id with the status 1 (Closed)
https://drive.google.com/drive/folders/1Rd_JzzeRm_T-ZMoS_J8a3BXCVKq1N2LN?usp=sharing
Thanks again
Hi @milomilo2020,
what is your desired outcome? From how I read your description, it seems like you already have what you are looking for.
cheers,
Sturla
HI, my desire outcome is the second table. It can be confusing that the status_id are 0 and 1. But this is different that the counts on the second table
Could you try to create a calculated column like this:
next_insertion_date =
VAR _action_id =
CALCULATE ( SELECTEDVALUE ( 'Table'[action_id] ) )
VAR _date =
CALCULATE ( SELECTEDVALUE ( 'Table'[insertion_date] ) )
VAR _tab =
CALCULATE (
MIN ( 'Table'[insertion_date] ),
FILTER (
ALL ( 'Table' ),
'Table'[insertion_date] > _date
&& 'Table'[action_id] = _action_id
)
)
RETURN
IF ( ISBLANK ( _tab ), DATE ( 2022, 12, 31 ), _tab )
and then a measure like this:
Count status =
VAR _date =
CALCULATE ( SELECTEDVALUE ( Dates[Value] ) )
VAR _status =
CALCULATE ( SELECTEDVALUE ( 'Status'[status] ) )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[action_id] ),
FILTER (
ALL ( 'Table' ),
_date >= 'Table'[insertion_date]
&& _date < 'Table'[next_insertion_date]
&& _status = 'Table'[status_id]
)
)
You will also need a date/calendar dimension.
This will give you this, based on the data you provided:
Use status_id from dimension instead of the fact_table in the table visual.
And in the measure replace this:
VAR _status =
CALCULATE ( SELECTEDVALUE ( 'fact_ds_action'[status_id] )
with this:
VAR _status =
CALCULATE ( SELECTEDVALUE ( 'dim_ds_status'[status_id] ) )
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |