Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all
I have data like below table
sequence | ID | Field 1 | Field 2 | Field 3 |
1 | A1 | ZZZ | ||
2 | A1 | YYY | ZZZ | |
3 | A1 | XXXX | YYY | KKK |
1 | A2 | |||
2 | A2 | XXXX | ZZZ | |
3 | A2 | MMM | ZZZ | |
4 | A2 | XXXX | YYY | ZZZ |
1 | A3 | XXXX | ||
2 | A3 | XXXX | ZZZ | |
1 | A4 | XXXX | YYY | ZZZ |
1 | A5 |
I want to create a new table based on below requirement
for ID = A1
Expected Output:
ID | Field 1 sequence | Field 2 sequence | Field 3 sequence |
A1 | sequence 3 | sequence 2 | sequence 3 |
A2 | sequence 4 | sequence 4 | sequence 2 |
A3 | sequence 1 | No Record | sequence 2 |
A4 | sequence 1 | sequence 1 | sequence 1 |
A5 | No Record | No Record | No Record |
Solved! Go to Solution.
@Anonymous
Try this MEASURE
Field 1 Sequence = VAR LastSeq = CALCULATE ( MAX ( Table1[sequence] ), Table1[Field 1] <> "" ) VAR ValueAtLastSeq = CALCULATE ( VALUES ( Table1[Field 1] ), Table1[sequence] = LastSeq ) VAR MinSeq = CALCULATE ( MIN ( Table1[sequence] ), Table1[Field 1] = ValueAtLastSeq ) VAR LastChange = TOPN ( 1, CALCULATETABLE ( VALUES ( Table1[sequence] ), FILTER ( Table1, [sequence] < LastSeq && [Field 1] <> ValueAtLastSeq && [Field 1] <> "" ) ), CALCULATE ( MAX ( Table1[sequence] ) ), DESC ) VAR Result = SWITCH ( TRUE (), NOT ( ISBLANK ( LastChange ) ), LastSeq, MinSeq < LastSeq, MinSeq, LastSeq ) RETURN IF ( ISBLANK ( Result ), "No Record", "Sequence " & Result )
@Anonymous
See file attached as well with your sample data
Thanks Zubair_Muhammad
Is it possible to create expression in calculated dimension? because I want to show data in a chart like below image
@Anonymous
You mean calculated column ??
because we cannot show MEASURES in Axis.... right??
I think its possible.
I will check and get back to you after a while as I got to go out now.
@Anonymous
Try this MEASURE
Field 1 Sequence = VAR LastSeq = CALCULATE ( MAX ( Table1[sequence] ), Table1[Field 1] <> "" ) VAR ValueAtLastSeq = CALCULATE ( VALUES ( Table1[Field 1] ), Table1[sequence] = LastSeq ) VAR MinSeq = CALCULATE ( MIN ( Table1[sequence] ), Table1[Field 1] = ValueAtLastSeq ) VAR LastChange = TOPN ( 1, CALCULATETABLE ( VALUES ( Table1[sequence] ), FILTER ( Table1, [sequence] < LastSeq && [Field 1] <> ValueAtLastSeq && [Field 1] <> "" ) ), CALCULATE ( MAX ( Table1[sequence] ) ), DESC ) VAR Result = SWITCH ( TRUE (), NOT ( ISBLANK ( LastChange ) ), LastSeq, MinSeq < LastSeq, MinSeq, LastSeq ) RETURN IF ( ISBLANK ( Result ), "No Record", "Sequence " & Result )