Forum Discussion
Distinct Count with value in same table
Another thing you may want to try then is instead of appending the two data tables, merge them side by side.
So intead of your original table, you end up with something like this:
CODE PREV_STATE CUR_STATE CHANGED 1015 A A TRUE 1016 A B FALSE 1017 B B TRUE If whatever index you're using for each row stays the same between days, this may be a better way to store your data. Be sure to check my previous reply for DAX code to solve your original problem
No problem, I can get wordy and over explain stuff. I also think I misunderstood your original example and was rolling it up so you only saw each count once.
Let me see if I can get your use case right, because it sounds like counting all the rows with the same code and state and appending that to each row wouldn't help much.
So you have sales data from X, and sales data from Y. You've merged them into one table, with a column identifying whether it's type X or Y data. In this data, there is a state signifying something about that day's data. What you're trying to accomplish is a way to easily see whether data with ID 123 in X is in a different state than data with the same ID 123 in Y?
Do you always have exactly two sets of data (previous and current), or is the plan for there to eventually be lots of data sets?
Also, if you still want the count added to each row, here's the DAX code to do that:
Result = CALCULATE( COUNT(Codes[Code]), FILTER(Codes, ([Code]&[State]) = (EARLIER([Code]) & EARLIER([State])) ) )
Just click your table, go to the Modeling tab, and add a new column.