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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
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.

October NL Carousel

Fabric Community Update - October 2024

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