Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |