Forum Discussion

SebKaliVith's avatar
SebKaliVith
Regular Visitor
8 years ago
Solved

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 !

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

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

8 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi SebKaliVith

     

    Try this MEASURE/Pattern

     

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

     

  • SebKaliVith's avatar
    SebKaliVith
    Regular Visitor
    8 years ago

    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

  • SebKaliVith's avatar
    SebKaliVith
    Regular Visitor
    8 years ago

    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's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      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's avatar
        SebKaliVith
        Regular Visitor
        8 years ago

        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's avatar
    SebKaliVith
    Regular Visitor
    8 years ago

    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