Forum Discussion
Creating Calculated Table With some filtering based on row values
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
4 Replies
- DoubleJSolution Supplier
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- markharoldrRegular Visitor
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
- DoubleJSolution Supplier
how long does it take to compute the calculated column?