Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello Everyone,
I want to create a calculated table based from this existing table:
The aim is to only include rows with items from Version, Item, Change columns where there is a change in value under Change.
Example Result:
Any clues on how to do this? Getting confused for the formula for DAX with this one.
Regards,
Mark
Hi
I did this in 3 steps:
1. In Power Query add an Index row (row is called ID)
2. Add a custom column "HasChanged" in DAX. Check if the value of the "Change" column has switched (compared to the row with closest lower ID). Output is "true" or "false"
3. Create a table using only the rows with "HasChanged" = "true"
Add Index column in PQ (step 1):
Formula for calculated column (step 2):
HasChanged = /*Calculated Column => every row in the table is iterated*/
VAR _ID = Versions[ID] /*ID of current row*/
VAR _Change = Versions[Change] /*Change of current row*/
VAR _PrevID = /*Get the ID of the previous row*/
MAXX(
FILTER(
Versions,
Versions[ID] < _ID),
Versions[ID])
VAR _ChangePrevID = /*Get Change of previous row*/
MINX(
FILTER(
Versions,
Versions[ID] = _PrevID),
Versions[Change])
RETURN _Change <> _ChangePrevID /*Check if "Change" has a different value than in previous row*/
Formula for calculated table (step 3):
Versions with changes = CALCULATETABLE(Versions,Versions[HasChanged])
I'm sure there are more elegant and compact ways to do this. Hope this helps anyway.
JJ
Thanks @DoubleJ . It works as expected and I need to sort first the table by Item and Version in power query. Though in a large data set around 100k rows it seems it is taking a while to compute the calculated column.
Appreciated your answer 🙂
Regards,
Mark
how long does it take to compute the calculated column?
Around 15 minutes for 100k rows
User | Count |
---|---|
83 | |
69 | |
68 | |
65 | |
54 |
User | Count |
---|---|
93 | |
91 | |
91 | |
76 | |
70 |