Forum Discussion

Jayasimha's avatar
Jayasimha
Frequent Visitor
4 years ago
Solved

Exclude/Include measure in slicer based on Yes/No column

Hello,

I have a column in my data which has Yes or No. Below is a sample

Accounts?Revenue
Yesxxx
Yesxxx
Noxxx

 

I was trying to create a measure and put it into a slicer. I created a new table with no connections. It has only two rows i.e., Exclude and Include. If the slicer is filtered on Exclude then it should show reveue for all Yes and if Include is selected then it should show revenue for all(both Yes and No)
Below is the measure I created unsuccessfully

Yes/No =
SWITCH(SELECTEDVALUE('NewTable'[Include/Exclude]),"Exclude",CALCULATE(SUM(Data[Revenue]),"Yes"),
"Include",SUM(Data[Revenue]))

  

At first I tried to create without the revenue field which did not work. Then I included revenue field.

 

Please help me correct this. Can this even be done?

Thank you

 

PowerBI 

Need Help
  • selimovd's avatar
    selimovd
    4 years ago

    OK, now I get it 😊

    Then your approach with the new table should work.

    Then create the following measure:

    Yes/No =
VAR vSlicerValue = SELECTEDVALUE ( 'NewTable'[Include/Exclude] )
RETURN
    SWITCH (
        vSlicerValue,
        "Exclude",
            CALCULATE (
                SUM ( Data[Revenue] ),
                Data[Accounts?] = "Yes"
            ),
        "Include", SUM ( Data[Revenue] )
    )

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

     

4 Replies

  • ERD's avatar
    ERD
    Community Champion
    4 years ago

    Hi Jayasimha ,

    So you have 2 unconnected tables: data and newTable.

    You can try this measure:

    RevenueAmt = 
VAR _check = SELECTEDVALUE ( 'NewTable'[Include/Exclude] )
RETURN
    IF (
        ISFILTERED ( 'NewTable'[Include/Exclude] ),
        IF (
            _check = "Exclude",
            SUMX(FILTER('Table', 'Table'[Accounts] = "Yes"), 'Table'[Revenue]),
            SUM ( 'Table'[Revenue] )
        ),
        SUM ( 'Table'[Revenue] )
    )

    If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    4 years ago

    Hey Jayasimha ,

     

    you cannot use measures as slicers. This only works in a few scenarios.

    Why don't you just use the column myTable[Accounts?] for the slicer? There you can slice for yes or no.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • Jayasimha's avatar
      Jayasimha
      Frequent Visitor
      4 years ago

      Hi selimovd,
      Thank you for taking time and going through my question.
      In the slicer I want to give only Yes or ALL(including both Yes and No). I don't want to give the user an option to select No as the revenue doesn't make sense when selected only for No. 
      Can a new column be included in slicers?

      • selimovd's avatar
        selimovd
        Most Valuable Professional
        4 years ago

        OK, now I get it 😊

        Then your approach with the new table should work.

        Then create the following measure:

        Yes/No =
VAR vSlicerValue = SELECTEDVALUE ( 'NewTable'[Include/Exclude] )
RETURN
    SWITCH (
        vSlicerValue,
        "Exclude",
            CALCULATE (
                SUM ( Data[Revenue] ),
                Data[Accounts?] = "Yes"
            ),
        "Include", SUM ( Data[Revenue] )
    )

         

        If you need any help please let me know.
        If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
         
        Best regards
        Denis