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
For your reference.
I will be able to give you a detailed answer after I receive the specific data from you, but I think the problem can be solved by changing the data format of the "Date" column to "Date" format and unpivoting the various status columns and setting them in a slicer.
https://1drv.ms/f/c/98bccde05d2ed96b/EhQINNqFY0NPjUkfWQc1UVEB7CdgLoMdvbDrgwsuPuk4PA?e=ltRQGV
I understand changing date to correct data type and how unpivoting can help in visualization but an accumulated fact is supposed to track states of a business process. So, I have used flag columns if I used string column this would not be an issue. I am a bit stuck here would appreciate feedback on are accumulated fact tables not supposed to be made like this?