Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Friends
Could you please help me to update the below formula to acheive case 4 of below requirement.
Using this formula I have acheived till 3rd requirement
Formula
Baseline Variation =
var a = if(ISBLANK(Sheet1[Result]),0,Sheet1[Result])
var b = maxx(filter(Sheet1,Sheet1[ID]=EARLIER(Sheet1[ID]) && Sheet1[Flag]="Y"
&& Sheet1[Test]]=EARLIER(Sheet1[Test]])
&& Sheet1[Cat]=EARLIER(Sheet1[Cat]) ),Sheet1[Result])
var c =IF(ISBLANK(b),0,b-a)
return if(Sheet1[STATUS]="NOT DONE",BLANK(), c)
Requirement
Case 1 : ID having multiple rows and 1 row having Y value in Flag Field
ID Test Cat Flag STATUS Result Baseline Variation
1 A C1 5 10
1 A C1 10 5
1 A C1 Y 15 0
1 A C1 25 10
1 A C1 35 20
Case 2 : ID having single row and No Y value present in Flag Field, Set baseline as 0
ID Test Cat Flag STATUS Result Baseline Variation
2 B C2 5 0
Case 3 : IDs have STATUS field value = NOT DONE, set baseline as blank
ID Test Cat Flag STATUS Result Baseline Variation
3 C C3 NOT DONE
Case 4 : IDs have Result field value = blank, set baseline as blank
ID Test Cat Flag STATUS Result Baseline Variation
4 D C4
Hi @Renjith
is your problem solved?
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials
Hi @Renjith ,
try this
Baseline Variation =
VAR a =
IF ( ISBLANK ( Sheet1[Result] ), 0, Sheet1[Result] )
VAR b =
MAXX (
FILTER (
Sheet1,
Sheet1[ID] = EARLIER ( Sheet1[ID] )
&& Sheet1[Flag] = "Y"
&& Sheet1[Test] = EARLIER ( Sheet1[Test] )
&& Sheet1[Cat] = EARLIER ( Sheet1[Cat] )
),
Sheet1[Result]
)
VAR c =
IF ( ISBLANK ( b ), 0, b - a )
RETURN
IF ( Sheet1[STATUS] = "NOT DONE" || ISBLANK ( Sheet1[Result] ), BLANK (), c )
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |