Forum Discussion
Visualizing Accumulated Fact table with flag columns for a date dynamically
- 2 years ago
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
Thanks a lot for your detailed reply. It gave me an idea to solve it by changing my approach a little.
Happy to help 🙂