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
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
https://1drv.ms/f/c/98bccde05d2ed96b/EhQINNqFY0NPjUkfWQc1UVEB7CdgLoMdvbDrgwsuPuk4PA?e=ltRQGV
is it the wrong approach to use flag columns in a accumulated fact table?. Would appreciate if you can guide me in correct direction.
- Ritaf19832 years ago
Super User
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
- abdulhaseebm232 years agoRegular Visitor
Thanks a lot for your detailed reply. It gave me an idea to solve it by changing my approach a little.
- Ritaf19832 years ago
Super User
Happy to help 🙂