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
7 Replies
- Ritaf1983
Super User
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.
- Ritaf1983
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
- mickey64
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?
