Forum Discussion
Count changes in a value/column
This turned out to be context issue that was solved by using ALLSELECTED versus ALL:
Changed Column = VAR __DateTime = [DateTime] VAR __PreviousDateTime = MAXX(FILTER(ALLSELECTED('Table'),[DateTime]<__DateTime),[DateTime]) VAR __PreviousPart = MAXX(FILTER(ALLSELECTED('Table'),[DateTime]=__PreviousDateTime),[Part]) RETURN IF([Part]=__PreviousPart,0,1)
Locco There is nothing incorrect about Changed Column GJD. It is based on Part, not TD and correctly identifies when the Part has changed. You can use a column or measure. A measure is generally advised if you want the calculation to be dynamic such as based on user input/interaction. Measures are also preferrable in the sense that they do not add to the space consumed by the data model to the same extent as columns. I added a column for the TD calculation. See updated PBIX file attached.
Thanks Greg_Deckler ,
I've been going over this for hours and I can't figure out what's going on. The only difference between my data and yours is that mine is a merged query and I have much more data.
In your book the formulas work just fine, but in mine they don't produce the same results. The columns produce the incorrect values we've discussed already, and when I use the measure nothing ever populates. On a card it stays blank, and you can see the little circle "thinking" spinning icon in the corner.
Does the fact that I'm merging queries matter?
Back to my original statement regarding multiple tables...
If this formula works for part:
Changed Column =
VAR __DateTime = [DateTime]
VAR __PreviousDateTime = MAXX(FILTER('Table',[DateTime]<__DateTime),[DateTime])
VAR __PreviousPart = MAXX(FILTER('Table',[DateTime]=__PreviousDateTime),[Part])
RETURN
IF([Part]=__PreviousPart,0,1)How would I get it to work if PART was on another table? This is the problem I have with TD and ST, those are on another table. I merged queries to bring all the data together, but I don't know if that is causing my issues with the incorrect calculated columns.
It finally updated the card with "Changed Measure Total TD" and instead of the 34 it says in your book, it says 52k. It seems like the measure does not see the filter(s) which have been applied and is applying the measurement to the entire column regards of any filtered/sliced value.
Edit: Was able to fix this by changing ALL in the measure formula to VALUES, but the measure still returns the same value as the column formula (which is not the right value).
Locco Any possibility of you sharing your PBIX either via a shared link or privately via email. That's going to be the quickest resolution to this. Otherwise, I need a lot more information about what is going on in your model like how relationships are defined, etc. I can't imagine why merging the queries would cause a problem. When you are editing your formula for TD, are you catching both of the places you replace part?
Changed Column = VAR __DateTime = [DateTime] VAR __PreviousDateTime = MAXX(FILTER('Table',[DateTime]<__DateTime),[DateTime]) VAR __PreviousPart = MAXX(FILTER('Table',[DateTime]=__PreviousDateTime),[Part]) //change [Part] to [TD} here RETURN IF([Part]=__PreviousPart,0,1) // also need to change [Part] to [TD] hereThis turned out to be context issue that was solved by using ALLSELECTED versus ALL:
Changed Column = VAR __DateTime = [DateTime] VAR __PreviousDateTime = MAXX(FILTER(ALLSELECTED('Table'),[DateTime]<__DateTime),[DateTime]) VAR __PreviousPart = MAXX(FILTER(ALLSELECTED('Table'),[DateTime]=__PreviousDateTime),[Part]) RETURN IF([Part]=__PreviousPart,0,1)