Page filter gets ignored in measure

[a_gaisin]

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.

[nandukrishnavs]

@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/109...

[a_gaisin]

It works with BLANK(), thank you!

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

[nandukrishnavs]

@a_gaisin Can you explain your latest question?

[a_gaisin]

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

[nandukrishnavs]

@a_gaisin  Please provide sample data and expected output

[a_gaisin]

@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:

[nandukrishnavs · Accepted solution]

@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 🙂

[a_gaisin]

@nandukrishnavs thank you, it works!