Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Reference to the above query. Somehow my replies are getting automatically deleted, so created a new post here.
Thanks for the solution to the above-linked problem.
But my database is large and complex in comparison to what I shared, hence it's not quite working for me. Also, it's not the case where values would appear only for Q1.
Can someone replace quarters with index number and send me the revised output according to below table.
1 | 428 | 0 | 428 |
2 | 0 | 1 | 429 |
3 | 431 | 3 | 431 |
4 | 0 | 2 | 433 |
5 | 0 | 0 | 433 |
6 | 435 | 1 | 435 |
7 | 0 | 2 | 437 |
8 | 0 | 4 | 441 |
Basically in Excel, column D would be: if(B2>0, B2, C2+D1)
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
Expected result CC =
VAR _table =
ADDCOLUMNS (
Data,
"@condition",
IF (
Data[Value] <> 0
&& MAXX ( FILTER ( Data, Data[Index] = EARLIER ( Data[Index] ) - 1 ), Data[Value] )
IN { 0, BLANK () },
1,
0
)
)
VAR _grouptable =
ADDCOLUMNS (
_table,
"@group", SUMX ( FILTER ( _table, Data[Index] <= EARLIER ( Data[Index] ) ), [@condition] )
)
VAR _resulttable =
ADDCOLUMNS (
_grouptable,
"@result", IF ( Data[Value] > 0, Data[Value], Data[Add] )
)
VAR _resultaccumulate =
ADDCOLUMNS (
_resulttable,
"@accumulate",
SUMX (
FILTER (
_resulttable,
[@group] = EARLIER ( [@group] )
&& Data[Index] <= EARLIER ( Data[Index] )
),
[@result]
)
)
RETURN
MAXX (
FILTER ( _resultaccumulate, Data[Index] = EARLIER ( Data[Index] ) ),
[@accumulate]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
Expected result CC =
VAR _table =
ADDCOLUMNS (
Data,
"@condition",
IF (
Data[Value] <> 0
&& MAXX ( FILTER ( Data, Data[Index] = EARLIER ( Data[Index] ) - 1 ), Data[Value] )
IN { 0, BLANK () },
1,
0
)
)
VAR _grouptable =
ADDCOLUMNS (
_table,
"@group", SUMX ( FILTER ( _table, Data[Index] <= EARLIER ( Data[Index] ) ), [@condition] )
)
VAR _resulttable =
ADDCOLUMNS (
_grouptable,
"@result", IF ( Data[Value] > 0, Data[Value], Data[Add] )
)
VAR _resultaccumulate =
ADDCOLUMNS (
_resulttable,
"@accumulate",
SUMX (
FILTER (
_resulttable,
[@group] = EARLIER ( [@group] )
&& Data[Index] <= EARLIER ( Data[Index] )
),
[@result]
)
)
RETURN
MAXX (
FILTER ( _resultaccumulate, Data[Index] = EARLIER ( Data[Index] ) ),
[@accumulate]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks for the solution. It seems to work, but if you don't mind can you just change the last 2 entries as the below number and see if the desired output comes up when you run the same code:
7 438 2 438
8 440 4 440
Again, apologies for not entering the table, as the replies are automatically getting deleted.
Edit: No worries, I found the small bug, that was giving erroneous error. The var _table should be just this:
var _table = addcolumns(data,"@condition",if(data[value]<>0,1,0))
Thanks a ton for the effort though. Really appreciate it.
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
13 |
User | Count |
---|---|
91 | |
86 | |
46 | |
28 | |
21 |