Using Measure as Dimension; Comparing Snapshots

[sjs2020]

Hello

We are trying to analyze data from a table that has snapshots of data. User needs to select 2 dates and the analysis has to  report on the changes between the new dates.

site_id_id	node_id	snapshotdate	status
S1	S1_N1	1/1/2021	In Progress
S1	S1_N1	1/5/2021	In Progress
S1	S1_N1	1/10/2021	Complete
S1	S1_N2	1/10/2021	In Progress
S1	S1_N3	1/1/2021	In Progress
S1	S1_N3	1/5/2021	In Progress
S2	S2_N1	1/1/2021	In Progress
S2	S2_N1	1/5/2021	In Progress
S2	S1_N1	1/10/2021	In Progress

Notes:

S1_N1   No Progress                       Between 1-Jan and 5-Jan

S1_N1   Progressed                         Between 5-Jan and 10-Jan

S1_N2   Not Present in 1-Jan and 5-Jan, Added in 10-Jan Snapshot                                                            

S1_N3   Present in 1st and 5th Jan, Deleted 10th-Jan       

Viz that I am trying to achieve:

Progress Status	Node Count
No Progress	2
Progressed	4
Deleted	2

Steps taken so far:

• When I try to create “Calculated Columns”, its per row and I cannot use it, created “Measures” (formula below), I was able to get the data side by side for selected 2 periods.
• The issue is that these measures can’t be used to group values .
• Progress field is what I want as a group by/dimension field.

Begining Status = VAR _minAll = CALCULATE(MINX(node_status_ss,[Snapshot Date]),ALLSELECTED()) VAR _minSelected = MIN(node_status_ss[Snapshot Date]) VAR _maxAll = CALCULATE(MAXX(node_status_ss,[Snapshot Date]),ALLSELECTED()) VAR _maxSelected = max(node_status_ss[Snapshot Date]) RETURN   IF(       (_minAll=_minSelected && _maxAll = _maxSelected) -- Node in Both StartingSS & EndingSS       ||    (_minAll = _minSelected )                        -- Only in  EndingSS         , CALCULATE(MIN(node_status_ss[Status]),node_status_ss[Snapshot Date]=_minAll)     )

Ending Status = VAR _minAll = CALCULATE(MINX(node_status_ss,[Snapshot Date]),ALLSELECTED()) VAR _minSelected = MIN(node_status_ss[Snapshot Date]) VAR _maxAll = CALCULATE(MAXX(node_status_ss,[Snapshot Date]),ALLSELECTED()) VAR _maxSelected = max(node_status_ss[Snapshot Date]) RETURN   IF(       (_minAll=_minSelected && _maxAll = _maxSelected) -- Node in Both StartingSS & EndingSS       ||    (_maxAll = _maxSelected )                        -- Only in  EndingSS         , CALCULATE(MAX(node_status_ss[Status]),node_status_ss[Snapshot Date]=_maxAll)     )

Any help is appreciated.

[sjs2020]

Great, the video was clear and well explained. Thank you so much! Any iputs on how to make the Calculated measure filtrable by the user?

[amitchandak · Accepted solution]

@sjs2020 , Not able to get it completely. But two make measure to work as dimension you need bucketing with an independent table. Then you have to create new measures with Grouping(values/summarize) and using a between or equal to filter with the new table. 

refer my video if that can help - https://www.youtube.com/watch?v=CuczXPj0N-k