Forum Discussion

abdulhaseebm23's avatar
abdulhaseebm23
Regular Visitor
2 years ago
Solved

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. Let...
  • Ritaf1983's avatar
    Ritaf1983
    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