Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Count Rows based on dynamic column value

Hi, I have a stacked bar chart with a line graph on and as a tooltip, I want to show the percentage value of each stack. I've tried various solutions, but to no avail. For example, for 2019, when I hover over the bottom stack, I want the tooltip to tell me that it represents XX% of uploads for non-partners that year.

 

I've tried the solution below, but the latter part of the argument returns the total number of rows within the table, not the amount for each year.

 

Percentage = DISTINCTCOUNT('Overview'[First_NID])/CALCULATE(DISTINCTCOUNT(Overview[First_NID]),ALLEXCEPT(Overview,Overview[First_Year]))

9 REPLIES 9
Anonymous
Not applicable

I smell a rat here... The rat being a single-table model. Do you happen to have a model with just one big table?

Anonymous
Not applicable

 

So it's using the two tables I've highlighted.

So it's using the two tables I've highlightedSo it's using the two tables I've highlighted

amitchandak
Super User
Super User

@Anonymous , try a measure like


Percentage = divide(DISTINCTCOUNT('Overview'[First_NID]),CALCULATE(DISTINCTCOUNT(Overview[First_NID]),filter(allselected(Overview),Overview[First_Year]) =max(Overview[First_Year])))

Anonymous
Not applicable

Thanks, @amitchandak! However, it's still not working. I receive the following error message - A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Anonymous
Not applicable

Show the model.

Anonymous
Not applicable

When you say the model, are you referring to the underlying data?

Anonymous
Not applicable

No, I'm referring to the schema.

Anonymous
Not applicable

It's quite large, but the attached screenshot shows the two tables I'm using. CMD Model.PNG

Anonymous
Not applicable

OK, I can tell you straight away you should change the way you model your data. It would take a small book to explain to you why the model is bad. But the fact is: it's very bad. I mean VERY. And I'm not talking about the tables. I'm talking about the relationships. Bi-dir relationships have been thought to solve some very specific issues. Namely, those with many to many relationships that require a bridge table. Nothing else. Second, the *-* (many-to-many) relationships have been designed to solve the issue of granularity and bi-dir filtering should never be enabled on them. If you leave the model as it is right now, very soon---or even now but without being aware---your model will start producting figures nobody will be able to explain and understand. Including you.

 

I'm almost sure this model is ambiguous but the engine does not signalize it.

 

Please do not make the mistakes other have. Change the model according to Best Practices of PBI modeling and dimensional modeling. This advice is all I can do for you right now. Ignore it and you'll be in for troubles later on BIG TIME.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.