Forum Discussion
Visualizing Accumulated Fact table with flag columns for a date dynamically
I have developed a accumulated fact table in power bi for payment tracking with flag columns like pending, processing, on hold, complete, cancelled etc. I am having issues on how to visualize it. Lets say i want to filter the table on a date range and see how many orders are in processing or cancelled I can use a date slicer to control the date range. But how can I ensure it counts only the latest state of each order. Since a order can have multiple states I want to count only the latest state. I have a datetime column so try max on date column but what if i wanted to answers these question for a date dynamically?
Hi abdulhaseebm23
To illustrate the differences between working with many columns for each status with a true/false flag and an unpivoted table with only one column for the status, I took a partial snapshot of your fact table.
Let's begin with your version.
To show all the statuses I'll need to create a separate measure for every status :Closed # = CALCULATE(DISTINCTCOUNT('Tracking columns'[Order Id]),'Tracking columns'[CLOSED]=true())Completed # = CALCULATE(DISTINCTCOUNT('Tracking columns'[Order Id]),'Tracking columns'[COMPLETE]=true())on hold # = CALCULATE(DISTINCTCOUNT('Tracking columns'[Order Id]),'Tracking columns'[ON_HOLD]=true())ETC...
"Beyond the challenge of managing individual measures, the visualization aspect presents additional complexities. When columns and measures are disaggregated, the absence of a unifying 'category' results in visualizations that appear as follows:
Now let's see the unpivot method as mickey64 suggested :
after unpivot in PQ we will get that table like the following :
Now we can create only one measure that counts the true status :
Status # = CALCULATE(DISTINCTCOUNT('Unpivoted status'[Order Id]),'Unpivoted status'[Status]=TRUE())The graphs will look like :
The pbix with the example is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
