topic Re: Page filter gets ignored in measure in DAX Commands and Tips

Page filter gets ignored in measure

a_gaisin — Sun, 24 May 2020 14:14:38 GMT

Hello, I’m quite new to Power Bi and DAX, and encountered a problem, when page filter is ignored when a measure is calculated.

I have a table with project numbers, for each project there exists a certain number of entries in the table. There’s also a page filter that filters only certain subset of projects. For filtered subset of projects I need to calculate the number of entries that have their value in column called “Code” equal to “ABC”.

To do it, I created the following measure:

Count ABC = CALCULATE(COUNT(Table[‘Code’]), Table[‘Code’]=“ABC”)

I also added the following measure that counts overall number of entries(with any code) for every filtered project:

Count # codes = COUNTROWS(Table)

Adding these two measures in Matrix visual gives the following result:

Now, I’m trying to add a third measure, that is supposed to take values based on a value of Count ABC. In my case, it should be equal to 1 if value of Count ABC equals 1, and 0 otherwise.

Here’s the code:

Count ABC with IF = IF(CALCULATE(COUNT(Table[‘Code’]),Table[‘Code]=“ABC”)=1,1,0)

When added to a Matrix visual (see image excerpt below), this measure completely ignores the page filter, and computes values for a set of all projects.

How can I force it to comply with the page filter?

Thank you very much for help.

Re: Page filter gets ignored in measure

nandukrishnavs — Sun, 24 May 2020 14:21:38 GMT

a_gaisin

Try this

Count ABC with IF = VAR x = CALCULATE ( COUNT ( 'Table'[Code] ), 'Table'[Code] = "ABC" ) RETURN IF ( x = 1, 1, BLANK () )

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Here is my latest blog

https://community.powerbi.com/t5/Community-Blog/Dynamic-Page-Navigation-Based-on-User-Login/ba-p/1097786

Re: Page filter gets ignored in measure

a_gaisin — Sun, 24 May 2020 14:42:10 GMT

It works with BLANK(), thank you!

But how to make it work if I want it to equal to 0 or any other value?

Re: Page filter gets ignored in measure

nandukrishnavs — Sun, 24 May 2020 15:00:56 GMT

a_gaisin Can you explain your latest question?

Re: Page filter gets ignored in measure

a_gaisin — Sun, 24 May 2020 15:05:15 GMT

I want the measure equal to 0, not BLANK, in case of FALSE. How can I make it happen?

Re: Page filter gets ignored in measure

nandukrishnavs — Sun, 24 May 2020 15:24:16 GMT

a_gaisin Please provide sample data and expected output

Re: Page filter gets ignored in measure

a_gaisin — Mon, 25 May 2020 15:49:49 GMT

nandukrishnavs , please take a look at this pbix file (google drive) https://drive.google.com/file/d/1QawaQQncB0k6eaTUbuuF22vN8nrCyyRY/view?usp=sharing

Expected output looks like this:

Re: Page filter gets ignored in measure

nandukrishnavs — Mon, 25 May 2020 16:40:17 GMT

a_gaisin

Try this

Count ABC with IF = VAR _initsymbol = DISTINCT ( symbol_type[init_symbol] ) VAR _projectids = CALCULATETABLE ( DISTINCT ( 'Table'[project id] ), FILTER ( ALL ( 'Table' ), 'Table'[first_symbol] IN _initsymbol ) ) VAR _count = CALCULATE ( COUNT ( 'Table'[code] ), 'Table'[code] = "ABC" ) VAR _result = IF ( ISBLANK ( _count ), 0, _count ) RETURN IF ( SELECTEDVALUE ( 'Table'[project id] ) IN _projectids, _result, BLANK () )

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Re: Page filter gets ignored in measure

a_gaisin — Tue, 26 May 2020 12:25:47 GMT

nandukrishnavs thank you, it works!