Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
SebKaliVith
Regular Visitor

IF column filtered then

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 !

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

602.png


Regards
Zubair

Please try my custom visuals

View solution in original post

8 REPLIES 8
SebKaliVith
Regular Visitor

@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

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


Regards
Zubair

Please try my custom visuals
SebKaliVith
Regular Visitor

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 appearThe A bar doesn't appear

When filtered, the B bar doesn't appearWhen 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

 

 

 

 

 

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

602.png


Regards
Zubair

Please try my custom visuals

@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
Regular Visitor

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
Community Champion
Community Champion

Hi @SebKaliVith

 

Try this MEASURE/Pattern

 

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

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.