Forum Discussion

ZSF0003's avatar
ZSF0003
Helper I
2 years ago

Parameter on Legend

Hello,

I have a simple visual that displays a distinct count of referralIDs by referral date like so:


I've created a parameter for the legend to allow the user to apply different slicers to the visual. The parameter is defined as such:

Parameter = {
    ("Payor Groupings", NAMEOF('Portal Referrals'[Billed Payor (groups)]), 0),
    ("Individual Payor", NAMEOF('Portal Referrals'[Billed Payor]), 1),
    ("Member State", NAMEOF('Portal Referrals'[MemberState]), 2),
    ("Item Group", NAMEOF('Portal Referrals'[ItemCategory]), 3),
    ("Company", NAMEOF('Portal Referrals'[ReferralCompany]), 4)
}
'Portal Referrals' being the name of the parent data source creating this visual. Some slicers tie out perfectly (Note how the total labels match the counts from the previous screenshot):

 

Others do not:

 

Why? It appears the total label is a count rather than a distinct count. In this table visual using the same parameter and distinct count of referralID, we see the total match the first two visuals, but if you add up the column, it equals 3,852 which is what the incorrect bar chart labels for June 2024.


Why does the visual work differently in table format vs. stacked column chart?


Furthermore, when I use the parameter for individual payor using the same month, I see a similar dynamic. The table shows the correct total of 3,075 but the stacked column chart shows a count rather than distinct count of 3,167. This gets worse when I take away the date filter:

This

Becomes

 

Why did it shrink? Shouldn't it at least show 3,167??

Lastly, the desired end result is for the parameter to reference one-to-many dimension tables so that I can use filters that interact with visuals using different data sets. Are there any common pitfalls I should be aware of? Will nulls cause issues? Should filters and parameter slicers interact?

Thank you in advance.


Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ZSF0003 ,

    The discrepancy between the table and the stacked column chart is likely due to how Power BI aggregates data in different visual types. In a table, each row is treated individually, so the distinct count is accurate. However, in a stacked column chart, Power BI might be summing up the counts for each category, leading to an inflated total.

    Measure = DISTINCTCOUNT('Table'[ID])

    When you remove the date filter, the total count should not shrink if the distinct count logic is applied correctly. This might be due to how the data is being filtered or aggregated.

    Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[ID]), ALL('Table'[Date]))

    You can create a calculated column to replace nulls with a default value.

    Measure 3 = IF(ISBLANK(MAX('Table (2)'[ID])),"Unknown",MAX('Table (2)'[ID]))

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • ZSF0003's avatar
      ZSF0003
      Helper I
      2 years ago

      This didn't seem to solve the issue. I think there is a misunderstanding as to how stacked column chart totals are supposed to work. I'm curious if anyone else has noticed this. I tried the ALL calc, and the total label still appears to be a sum of the distinct counts rather than a distinct count itself. That's not the only oddity, but it is a frustrating issue.