topic Re: Count Rows based on dynamic column value in DAX Commands and Tips

Count Rows based on dynamic column value

Anonymous — Fri, 18 Jun 2021 12:24:07 GMT

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]))

Re: Count Rows based on dynamic column value

Anonymous — Sat, 19 Jun 2021 18:54:15 GMT

Show the model.

Re: Count Rows based on dynamic column value

Anonymous — Mon, 21 Jun 2021 10:54:12 GMT

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

Re: Count Rows based on dynamic column value

amitchandak — Wed, 23 Jun 2021 07:59:12 GMT

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])))

Re: Count Rows based on dynamic column value

Anonymous — Thu, 24 Jun 2021 10:41:44 GMT

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.

Re: Count Rows based on dynamic column value

Anonymous — Thu, 24 Jun 2021 12:13:12 GMT

No, I'm referring to the schema.

Re: Count Rows based on dynamic column value

Anonymous — Thu, 24 Jun 2021 12:15:19 GMT

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

Re: Count Rows based on dynamic column value

Anonymous — Fri, 25 Jun 2021 14:06:27 GMT

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

Re: Count Rows based on dynamic column value

Anonymous — Fri, 25 Jun 2021 14:07:28 GMT

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

Re: Count Rows based on dynamic column value

Anonymous — Fri, 25 Jun 2021 17:16:32 GMT

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.