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?
I explain it better. It's the same database (unfortunately the software who generates this data is outdated), but I created an external query that generates the entire database daily and stores it in a .csv file. Then, in a designated folder, I store them.
In power query I configured a query that uses that folder as a data source, and what I do is identify the most recent one and the one immediately above with a tag. What I need is to easily filter those data with changes in a field, from one day to the next. That is why I would like to create a custom column that stores the number of different values in a field for each record, knowing that the final table has two records with ID 123, one with the "Previous" tag and the other with the "Current" tag.
I could then use that column as the key field in a filter object where I list all records with more than a single value for the analyzed field (state).
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
Thanks, I'll try later the option with EARLIER (I could not understand very well the logic of that function). I had previously done that table through a query, so that solution was perfect for me. It would be good to know if Power Query really has better performance than DAX (I think so).
Thank you.As far as performance, I think it depends on what you're doing. Filtering and cutting down a large data set, I believe PowerQuery to be the better tool. But when it comes to calculating field values, especially complex ones, I think DAX is the way to go.
What EARLIER does is to step out the context a level. So when a DAX column is being calculated, it does the calculation for each cell. One of the shorthand things we do is say [column] to refer to the entire column. EARLIER([column]) in a measure will step back one level and get the value for [column] on the current row. This can be used to step out of GROUPBY clauses as well.