IF column filtered then

[SebKaliVith]

Hi everyone !

Here is how the data look like in my report :

NAME     X       Y      Z

   A         3%   10% 

   B         1%   7%

   A         5%   6%

   B         1%   11% 

   C         2%   4%

Here is what i would like to reach :

IF COLUMN NAME IS FILTERED, THEN, COLUMN Z SCREEN THE DATA IN COLUMN X, ELSE, COLUMN Z SCREEN THE DATA IN COLUMN Y

Is there a formule or a measure to reach this goal ?

Thanks a lot !

[SebKaliVith]

@Zubair_Muhammad

It's perfect, i succeed to do whait i wanted with your help.

Thanks a lot for all.

Juste a small question for my personal understanding of the solution :

To detect if column "NAME" was filtered or not, i created a measure "IsFiltered" which whas :

IF(ISFILTERED[NAME]);"1";"0")

This formule was good because, when i display the measure in a card, it switched good between 1 and 0 when i filtered.

But when i write :

IF(ISFILTERED[NAME];SELECTEDVALUE[Y];SUM[X])

Thes formule doesn't work.

Any idea of why ?

Thanks

[Zubair_Muhammad]

Hi @SebKaliVith

This is interesting. I will explore this and get back to you

This is a good reference for ISFILTERED functions

https://msdn.microsoft.com/en-us/library/gg492163.aspx

[SebKaliVith]

Hi @Zubair_Muhammad,

It Works great ! Thanks a lot !

But, i need to display this result in a "Stacked Bar Chart".

When i set "NAME" in AXIS and "Z" in Value, the "A" bar doesn"t appear.

Plus, when i filter with "B", the "B" bar doesn't appear too.

The A bar doesn't appear

When filtered, the B bar doesn't appear

I presume it is due to "SELECTEDVALUE" which can choose only one value, but A has two differents values.

Is there a formule which can fix it ?

Like a SUM of the values in X when filtered, and a SUM of the values in Y when isn't filtered.

(Sorry to ask again, i am Excel/VBA expert but a noob beginner on PBI ...)

In all cases, thanks for your help !

Regards,

Séb

[Zubair_Muhammad · Accepted solution]

Hi @SebKaliVith

See the revised file here

I just changed selectedvalue to sum

Measure =
IF (
    COUNTROWS ( ALLSELECTED ( TableName[NAME] ) )
        = COUNTROWS ( ALL ( TableName[NAME] ) ),
    SUM ( TableName[x] ),
    SUM ( TableName[y] )
)

[SebKaliVith]

@Zubair_Muhammad,

Thanks a lot for your help, i watched the file and it looks like exactly what i need.

I am going to try the measure you wrote on my real PowerBI report this afternoon.

I come back here if i have other questions.

Thanks a lot for the time you took to help beginners in the community !

Best,

SB

[SebKaliVith]

HI Zubair,

Thanks a lot for your help !

I tried yesterday evening the solution of your first post, but unfortunately it didn't work.

I look the file you sent me and i reply here.

Thanks a lot for your help

[Zubair_Muhammad]

Hi @SebKaliVith

Try this MEASURE/Pattern

Measure =
IF (
    COUNTROWS ( ALLSELECTED ( TableName[NAME] ) )
        = COUNTROWS ( ALL ( TableName[NAME] ) ),
    SELECTEDVALUE ( TableName[x] ),
    SELECTEDVALUE ( TableName[y] )
)

[Zubair_Muhammad]

Hi @SebKaliVith

See attached file here