Forum Discussion
How to flag events
Hi JFAschoon , that's really strange, adding one column shouldn't increase it that much!
Perhaps it is a bug, do you want to try the suggestion here? https://community.fabric.microsoft.com/t5/Desktop/Pbix-file-size-increases-on-making-a-change-in-the-source-query/m-p/342738 (closing & re opening)
I wonder if PowerBI is trying to cache, eg all the steps.
In any case, there is a way that you can also achieve this in the front-end DAX. This means that the result will not take up space in the data model, and will also be dynamic.
I didn't have much time to fiddle about with it, so the formula is less simple than it needs to be! However here is what you can try.
ReturnVal - is a measure that just returns the current value in [col1]
FlagMeasure then uses the window function OFFSET to return the previous and next values of col1 and checks to see if either of those are "Stop"
returnVal = SELECTEDVALUE(srcTable[Col1])
FlagMeasure =
var prevVal = CALCULATE([returnVal],
OFFSET(-1,,
orderby(srcTable[Date of event],asc,
srcTable[ID],asc,
srcTable[Col1],asc,
srcTable[Col2],asc
)
))
var nextVal = CALCULATE([returnVal],
OFFSET(1,,
orderby(srcTable[Date of event],asc,
srcTable[ID],asc,
srcTable[Col1],asc,
srcTable[Col2],asc
)
))
return If("Stop" IN { prevVal,nextVal},1,"")
This is the result:
I would recommend hard coding it in the source though, simply because this is dynamic - so you will need to code extra to handle selections and the context in which the measure is applied. The behaiviour of this flag might not be what you are looking for.
Pi