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
Ok, I understand, could you give me a solution using DAX? Honestly, I got lost in your explanation. If you think It's possible with DAX, go ahead.
Maybe if I explain my scenario, it would be better for you.
I got 2 csv with the same structure, one of them has the transactions of yesterday, others of today.
I made a query, whose data source is a folder that contains these files, and creates a single table identifying Previous Data vs. Actual Data in a column. What I need is to easily filter the codes where the state changes from one period to another. And therefore, I thought about creating a column that would do the unique count of the states for each code using a filter object or a graph.
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.
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.