Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dynamic Consecutive Difference between rows based on filter selection and a primary column

Hi All,

 

I have a very interesting problem to share for which I am unable to find a solution. This is very much similar to calculating difference between consecutive rows. But the catch here is this calculation has to be dynamic based on filter selection. 

 

 Original data looks like this : 

IDValueType
431219Major
431243Major
4312337Major
430241Minor
4302337Minor
429130Major
429157Major
429218Major
429242Major
4292337Major
428246Minor
4282337Minor
4270Major
4271Major
4273Major
4275Major
4276Major
4277Major
4279Major

 

So I have grouped this data into matrix: 

IDValueType
4312799Major
4302578Minor
4293084Major
4282583Minor
42731Major

 

I want calculate difference between two consectuive rows which should ideally look like: 

IDValueTypeDiff
4312799Major221
4302578Minor-506
4293084Major501
4282583Minor2552
42731Major31

 

I am able to get the above result. But now I want to select filter based on type. E.g say Major is selected. I want the result to look like : 

IDValueTypeDiff
4312799Major-285
4293084Major3053
42731Major31

 

whereas I am getting this: 

IDValueTypeDiff
4312799Major221
4293084Major501
42731Major31

 

I tried creating a measure which will reset itself based on filter selection: 

Measure = CALCULATE(COUNT(Grouped_Auto_QA_Basic_Analysis[Index]), FILTER(ALLSELECTED(Grouped_Auto_QA_Basic_Analysis), Grouped_Auto_QA_Basic_Analysis[Index] <= MAX(Grouped_Auto_QA_Basic_Analysis[Index])))

 

But when I tried creating diff using formula: 

diff1 = var previndex = Grouped_Auto_QA_Basic_Analysis[Measure] -1
return
Grouped_Auto_QA_Basic_Analysis[campaign_nkw] - CALCULATE(VALUES(Grouped_Auto_QA_Basic_Analysis[campaign_nkw]),FILTER(ALLSELECTED(Grouped_Auto_QA_Basic_Analysis),Grouped_Auto_QA_Basic_Analysis[Measure] = previndex))

 

I am not able to get desired result using the formula. Can someone help me calculating dynamic difference based on filter selection? I tried doing this using Index but didn't workout. I also tried using R code, failed again. 

 

Thank you so much in advance. I am desperate to get this done as soon as possible. 

 

Cheers,

Shruti

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@Anonymous- Perhaps try something like:

 

Measure = 
VAR __currentID = MAX([ID])
VAR __tmpTable = SUMMARIZE(ALLSELECTED('Earlier'),[ID],"__Value",SUM('Earlier'[Value]))
VAR __tmpTable1 = ADDCOLUMNS(__tmpTable,"PreviousID",MAXX(FILTER(__tmpTable,[ID]<EARLIER([ID])),[ID]))
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"PreviousValue",MAXX(FILTER(__tmpTable1,[ID]=EARLIER([PreviousID])),[__Value]))
VAR __tmpTable3 = ADDCOLUMNS(__tmpTable2,"Difference",[PreviousValue] - [__Value])
RETURN MAXX(FILTER(__tmpTable3,[ID]=__currentID),[Difference])

'Earlier' is my table name. It's a bit verbose but I was going for step-by-step to better demonstrate the technique here. Definitely less verbose ways of solving this, but I think this demonstrates the technique in a manner that can be comprehended pretty easily.

 

If you put this Measure into a matrix, it will respect your ID filtering.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
Super User

@Anonymous- Perhaps try something like:

 

Measure = 
VAR __currentID = MAX([ID])
VAR __tmpTable = SUMMARIZE(ALLSELECTED('Earlier'),[ID],"__Value",SUM('Earlier'[Value]))
VAR __tmpTable1 = ADDCOLUMNS(__tmpTable,"PreviousID",MAXX(FILTER(__tmpTable,[ID]<EARLIER([ID])),[ID]))
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"PreviousValue",MAXX(FILTER(__tmpTable1,[ID]=EARLIER([PreviousID])),[__Value]))
VAR __tmpTable3 = ADDCOLUMNS(__tmpTable2,"Difference",[PreviousValue] - [__Value])
RETURN MAXX(FILTER(__tmpTable3,[ID]=__currentID),[Difference])

'Earlier' is my table name. It's a bit verbose but I was going for step-by-step to better demonstrate the technique here. Definitely less verbose ways of solving this, but I think this demonstrates the technique in a manner that can be comprehended pretty easily.

 

If you put this Measure into a matrix, it will respect your ID filtering.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.