Using Measure as Dimension; Comparing Snapshots

Question

HelloWe are trying to analyze data from a table that has snapshots of data. User needs to select 2 dates and the analysis has to&nbsp; report on the changes between the new dates.site_id_idnode_idsnapshotdatestatusS1S1_N11/1/2021In ProgressS1S1_N11/5/2021In ProgressS1S1_N11/10/2021CompleteS1S1_N21/10/2021In ProgressS1S1_N31/1/2021In ProgressS1S1_N31/5/2021In ProgressS2S2_N11/1/2021In ProgressS2S2_N11/5/2021In ProgressS2S1_N11/10/2021In ProgressNotes:S1_N1&nbsp;&nbsp; No Progress&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Between 1-Jan and 5-JanS1_N1&nbsp;&nbsp; Progressed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Between 5-Jan and 10-JanS1_N2&nbsp;&nbsp; Not Present in 1-Jan and 5-Jan, Added in 10-Jan Snapshot&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;S1_N3&nbsp;&nbsp; Present in 1st and 5th Jan, Deleted 10th-Jan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Viz that I am trying to achieve:&nbsp;Progress StatusNode CountNo Progress2Progressed4Deleted2&nbsp;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.&nbsp;&nbsp;Begining Status =&nbsp;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])&nbsp;RETURN&nbsp; IF(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (_minAll=_minSelected &amp;&amp; _maxAll = _maxSelected) -- Node in Both StartingSS &amp; EndingSS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ||&nbsp;&nbsp;&nbsp; (_minAll = _minSelected )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Only in&nbsp; EndingSS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , CALCULATE(MIN(node_status_ss[Status]),node_status_ss[Snapshot Date]=_minAll)&nbsp;&nbsp;&nbsp; )&nbsp;Ending Status =&nbsp;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])&nbsp;RETURN&nbsp; IF(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (_minAll=_minSelected &amp;&amp; _maxAll = _maxSelected) -- Node in Both StartingSS &amp; EndingSS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ||&nbsp;&nbsp;&nbsp; (_maxAll = _maxSelected )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Only in&nbsp; EndingSS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , CALCULATE(MAX(node_status_ss[Status]),node_status_ss[Snapshot Date]=_maxAll)&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;Any help is appreciated.

amitchandak · Accepted Answer

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.&nbsp; 
&nbsp;
refer my video if that can help - https://www.youtube.com/watch?v=CuczXPj0N-k

Forum Discussion

Using Measure as Dimension; Comparing Snapshots

2 Replies

Related content

degenerate dimensions

Power BI embedded in PowerPoint – Live /Snapshot/Public Snapshot buttons not working (recent regr?)

RLS on multiple dimensions

Warehouse Snapshots: Dropping tables deletes the tables from Snapshots

Data Snapshots

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