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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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

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

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

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

 

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!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.