cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
almeidajac Frequent Visitor
Frequent Visitor

New column with DISTINCTCOUNT ignores filters on page level

Hello! I am new to PowerBI but have been exploring it for a couple months now. I come from Excel and Access and having discovered now PowerBI, I believe I am going to use it more often.

 

I need to know which and how many items are most used across different projects.

 

Using "New column" (instead of "New measure"), I have created:

 

DistinctProjects = CALCULATE ( DISTINCTCOUNT (Table1[Project]); FILTER ( Table1; Table1[ItemNr] = EARLIER(Table1[ItemNr] ) )

 

 

When not filtering any of the projects on the slicer "Project", it shows the correct behaviour, i.e. e.g. there are 2 items utilized in 3 different projects. These 2 items are 'Item 01' and 'Item 02'.

 

Unfiltered.png

 

That's OK, but when I filter on the slicer for 'Project A' and 'Project B', it does not show the correct statistics, i.e. it should say that only 2 items are utilized in 2 different projects, and not 3 (because it is counting in all the projects universe of Table1 unfiltered).

filtered.png

 

HELP! I can I make it work?

 

Thanks for any hint or direction in the right path!

 

Cheers,

Jorge

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: New column with DISTINCTCOUNT ignores filters on page level

Hi @almeidajac,

 

I'm afraid it is not available to achieve such a requirement. You should be aware that the values resturned by calculated column are static once it is initialized. So, the [DistinctProjects] items displayed in visual won't be dynamically changed based on slicer. However, if you get DistinctProjects via measure, it could be dynamical, but measure will only show one single value in a visual without any context.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
almeidajac Frequent Visitor
Frequent Visitor

Re: New column with DISTINCTCOUNT ignores filters on page level

Community Support Team
Community Support Team

Re: New column with DISTINCTCOUNT ignores filters on page level

Hi @almeidajac,

 

I'm afraid it is not available to achieve such a requirement. You should be aware that the values resturned by calculated column are static once it is initialized. So, the [DistinctProjects] items displayed in visual won't be dynamically changed based on slicer. However, if you get DistinctProjects via measure, it could be dynamical, but measure will only show one single value in a visual without any context.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.