Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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 |
---|---|
103 | |
87 | |
77 | |
70 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |