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 :
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 :
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.
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 :
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 :
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
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
A Year in Review - December 2024
Find out what content was popular in the Fabric community during 2024.
