Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a set of data and I need to count the amount of times there were changes in a value.
My data is setup similar to this...
TableA
DateTime | Part | Quantity |
9/20/2021 12:00:00 am | 8250 | 720 |
9/20/2021 1:00:00 am | 9142 | 84 |
9/20/2021 2:00:00 am | 8250 | 800 |
9/20/2021 3:00:00 am | 8250 | 95 |
9/20/2021 4:00:00 am | 7748 | 91 |
9/20/2021 5:00:00 am | 9142 | 700 |
For this table, the returned value would be 5.
I'm also linking this to another table which has more details regarding the parts. This table is setup similar to this:
TableB
Part | TD | ST |
8250 | 10000 | 7314 |
9142 | 10001 | 7314 |
7748 | 10000 | 9548 |
4152 | 10002 | 9548 |
2829 | 10002 | 4314 |
5416 | 66148 | 7314 |
I'm linking table A and B through a relationship on "part." I'm pulling in the TD and ST values from Tableb and using them in the same visualization.
I need unique values for how many times parts were changed, how many times TD changed, and how many times ST changed.
Based on the supplied data, the values should be 5 (part changes), 4 (td changes), and 4 (st changes).
I've tried a few formulas I have found online but I can't get anything to work correctly, or to not just return count/distinct count.
Solved! Go to Solution.
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 So would this be correct for part changes, I assume it needs to account for both changes in parts and quantity?
Parts Changes =
COUNTROWS(
DISTINCT(
SELECTCOLUMNS('TableA',"Part",[Part],"Quantity",[Quantity])
)
) - 1
I'm not grasphing how to get the changes for TD and ST numbers you presented given your sample data, can you explain?
Thanks @Greg_Deckler,
That did not work, it only returns a distinct count and not how many times parts actually changed. If a part repeats later then that part isn't counted.
It's possible for a part to repeat multiple times in a column, in this case it would still count as a "1" since the part didn't actually change until a new part was loaded. If that part repeats later in the day, then it would count towards the change total.
For the TD and ST counts, I have merged the queries so this may no longer be an issue and if I get something to correctly count the part changes then that should also work for TD and ST changes.
Very similar issue here, but the solution there I could not get to work for me:
https://community.powerbi.com/t5/Desktop/Count-number-of-changes-of-the-value/m-p/487205
@Locco OK, try the MTBF approach. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
In your case:
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)
Thanks @Greg_Deckler ,
That still is not returning the desired result.
I've copied a small sample of my data, utilizing the formula:
UPDATE_DATE | PART | TD | Changed Column | Count of Changed Column | Changed Column |
08/16/2021 0:12 | 11164 | TD27414 | 1 | 1 | 1 |
08/16/2021 0:51 | 14054 | TD27955 | 1 | 1 | 1 |
08/16/2021 1:13 | 14470 | TD28112 | 1 | 1 | 1 |
08/16/2021 1:46 | 14490 | TD28312 | 1 | 1 | 1 |
08/16/2021 2:20 | 14093 | TD28412 | 1 | 1 | 1 |
08/16/2021 2:45 | 14075 | TD28412 | 1 | 1 | 1 |
08/16/2021 3:10 | 4344 | TD21038 | 1 | 1 | 1 |
08/16/2021 3:55 | 2165 | TD10454 | 1 | 1 | 1 |
08/16/2021 4:30 | 2008 | TD10291 | 1 | 1 | 1 |
08/16/2021 5:09 | 2006 | TD10449 | 1 | 1 | 1 |
08/16/2021 5:24 | 11438 | TD34994 | 1 | 1 | 1 |
08/16/2021 5:43 | 11436 | TD34994 | 1 | 1 | 1 |
08/16/2021 6:08 | 14168 | TD34112 | 1 | 1 | 1 |
08/16/2021 6:29 | 11444 | TD35234 | 1 | 1 | 1 |
08/16/2021 6:55 | 12255 | TD27456 | 1 | 1 | 1 |
08/16/2021 7:12 | 12254 | TD27456 | 1 | 1 | 1 |
08/16/2021 8:28 | 14324 | TD34142 | 1 | 1 | 1 |
08/16/2021 8:52 | 11380 | TD10294 | 1 | 1 | 1 |
08/16/2021 9:50 | 2920 | TD10329 | 1 | 1 | 1 |
08/16/2021 10:29 | 2918 | TD10296 | 1 | 1 | 1 |
08/16/2021 10:54 | 11299 | TD10242 | 1 | 1 | 1 |
08/16/2021 11:42 | 14290 | TD31375 | 1 | 1 | 1 |
08/16/2021 12:13 | 4294 | TD16872 | 1 | 1 | 1 |
08/16/2021 13:07 | 4283 | TD17754 | 1 | 1 | 1 |
08/16/2021 13:28 | 4293 | TD16973 | 1 | 1 | 1 |
08/16/2021 13:57 | 14251 | TD36196 | 1 | 1 | 1 |
08/16/2021 14:35 | 14054 | TD27955 | 1 | 1 | 1 |
08/16/2021 14:48 | 14056 | TD28013 | 1 | 1 | 1 |
08/16/2021 15:10 | 14327 | TD35036 | 1 | 1 | 1 |
08/16/2021 16:10 | 14326 | TD35036 | 1 | 1 | 1 |
08/16/2021 16:38 | 11649 | TD34116 | 1 | 1 | 1 |
08/16/2021 16:58 | 11436 | TD34994 | 1 | 1 | 1 |
08/16/2021 17:23 | 11438 | TD34994 | 1 | 1 | 1 |
08/16/2021 17:45 | 14168 | TD34112 | 1 | 1 | 1 |
08/16/2021 18:37 | 2987 | TD13073 | 1 | 1 | 1 |
08/16/2021 18:53 | 2987 | TD13073 | 1 | 1 | 1 |
08/16/2021 19:37 | 2165 | TD10454 | 1 | 1 | 1 |
08/16/2021 21:57 | 4282 | TD16692 | 1 | 1 | 1 |
08/16/2021 22:42 | 14182 | TD16893 | 1 | 1 | 1 |
08/16/2021 23:24 | 4343 | TD21038 | 1 | 1 | 1 |
08/16/2021 23:45 | 14289 | TD21038 | 0 | 1 | 0 |
There's a sum row that doesn't show up here. The first instance of "Changed Column" is with "No Summarization", second is with "Count", last is with "Sum" which returns the same value as "No Summarization."
It is not returning the correct # as a total, when it should give a 0 it is giving a 1. In a few instances the TD # repeats and I believe a 0 should populate.
(I've changed the formula parameters to "TD" instead of "Part" for this test')
@Locco Worked for me as a calculated column, which is why it was named "Changed Column". If you want it as a measure you will need:
Changed Measure =
VAR __DateTime = MAX('Table23'[UPDATE_DATE])
VAR __PreviousDateTime = MAXX(FILTER(ALL('Table23'),[UPDATE_DATE]<__DateTime),[UPDATE_DATE])
VAR __PreviousPart = MAXX(FILTER(ALL('Table23'),[UPDATE_DATE]=__PreviousDateTime),[PART])
RETURN
IF(MAX([PART])=__PreviousPart,0,1)
As a column as intended because it was named "Changed Column"
Also, the Total is a different issue: This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Thanks again @Greg_Deckler ,
I am using a calculated column and not measures.
I still don't believe the formula is returning the desired result. I've edited the picture you posted. In the circled area, should one of these repeating values be a 0? This is being counted as a change when it wasn't an actual change. There are other occurences of this where the TD did not change, but it seems to be counting as a change. It seems like it only worked on 1 line item where it returned a 0 (TD13073).
Am I misunderstanding?
@Locco The column is only based on part, what do you want it based on? I first said if the part changes. Is what you want is only if the TD changes? If so then replace the references for Part to TD.
My apologies @Greg_Deckler , I thought your screenshot was set on TD and not PART.
The desired result is still not being returned though, whether I use TD or PART as the column reference.
In the attached screenshot, I have created a column referencing TD and a column referencing PART. There are actually 38 part changes, but the value returned is 41. There are actually 33 TD changes, but the value returned is 40.
Regardless of which column is referenced, the returned value is not correct. When a change doesn't occur, the value in the table should be a 0, correct? If that is true, then there are many instances where it is counting a change when there were none.
Examples:
PART - 5:23 timestamp
TD - 2:45 timestamp, 5:43 timestamp, 7:12 timestamp, 10:53 timestamp, 6:53 timestamp
Those are all being counted as a change when it actually wasn't a change.
@Locco I don't get those results at all. See Table23 and Page 5 of attached PBIX below sig. Also the 5:23 PM line does change parts numbers from 11436 to 11438. All I can say is that either we have different data or you aren't translating the DAX correctly.
Thanks @Greg_Deckler, I appreciate your help and patience.
In your book, you are using measures. I've been using calculated columns and never used measures. You have a column, "Changed Column GJD," this also returns the same incorrect value which is in my book.
Should I be using measures or calculated columns?
@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).
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 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] here
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |