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
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.