Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Slicer with Measures - Select Multiple

Here's the sitch, I made a measure to track my credit card and bank account balances. I plot them on a line chart and use a slicer to choose the card/account. PROBLEM: I cannot select multiple cards or accounts. It is either one or all.

Here's the measure I use for the cards/accounts:

Apple Card Balance = CALCULATE(
       -SUM('YTD Cash & CC TD'[Paid Amount]),
       FILTER(ALLSELECTED(Dates),
       Dates[Date] <= MAX(Dates[Date])
        ),'YTD Cash & CC TD'[Account] = "Apple Card"
  )
 
Here's the measure used to reference a simple indexing table (that is actually being sliced):
Apple Card = IF(
      SELECTEDVALUE('CC Dimensions'[Index]) = 1
      || ISBLANK(SELECTEDVALUE('CC Dimensions'[Index])
      ),
      [Apple Card Balance]
)
 
The only established relationships are between my Dates table and YTD Cash & CC TD table that holds the transactional data.
 
Any ideas on how to be able to select multiple items in my slicer?
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hello @caseysmith6 ,

    Depending on your description, you can create this measure:

    Balance = 
VAR _account =
    SELECTEDVALUE ( 'CC Dimension'[Measure] )
VAR _total =
    CALCULATE (
        SUM ( 'YTD Cash & CC TD'[Paid Amount] ),
        FILTER ( ALLSELECTED ( Dates[Date] ), 'Dates'[Date] <= MAX ( 'Dates'[Date] ) )
    )
VAR account =
    CALCULATE (
        SUM ( 'YTD Cash & CC TD'[Paid Amount] ),
        FILTER ( ALLSELECTED ( Dates[Date] ), 'Dates'[Date] <= MAX ( 'Dates'[Date] ) ),
        'YTD Cash & CC TD'[Account] = _account
    )
RETURN
    IF ( NOT ( ISFILTERED ( 'CC Dimension'[Measure] ) ), _total, account )

    blance.png

    Attached a sample file in the next one, hopes to help you.

    Best Looks,
    Yingjie Li

    If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.

7 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    The SELECTEDVALUE is the problem.  You can try this to get around it.  Replace with the actual table and column with your cards in the slicer.

     

    NewMeasure = SUMX(VALUES(Table[CardColumn]), [Apple Card])

     

    There's probably a better way to construct the other measures, but this uses what you have now.

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      mahoneypat Thanks for the response. Where am I supposed to put this NewMeasure? I can't put it directly into the slicer (as in, it won't let me). The slicer is currently using the Measure column from this index table.

       

      Are you suggesting I abandon the Index table? Since that's where SELECTEDVALUE comes into play.

      Thanks!

       

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        Thank you for the additional info.  I think I misunderstood your model.  Why did you set up a measure slicer instead of a simple category slicer on your Account values?

         

        Did you consider a measure like this and a slicer on your Account column on the page?

         

        Card Balance = CALCULATE(
               SUM('YTD Cash & CC TD'[Paid Amount]),
               FILTER(ALLSELECTED(Dates),
               Dates[Date] <= MAX(Dates[Date]))
         

        If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

        Regards,

        Pat

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hello @caseysmith6 ,

    Depending on your description, you can create this measure:

    Balance = 
VAR _account =
    SELECTEDVALUE ( 'CC Dimension'[Measure] )
VAR _total =
    CALCULATE (
        SUM ( 'YTD Cash & CC TD'[Paid Amount] ),
        FILTER ( ALLSELECTED ( Dates[Date] ), 'Dates'[Date] <= MAX ( 'Dates'[Date] ) )
    )
VAR account =
    CALCULATE (
        SUM ( 'YTD Cash & CC TD'[Paid Amount] ),
        FILTER ( ALLSELECTED ( Dates[Date] ), 'Dates'[Date] <= MAX ( 'Dates'[Date] ) ),
        'YTD Cash & CC TD'[Account] = _account
    )
RETURN
    IF ( NOT ( ISFILTERED ( 'CC Dimension'[Measure] ) ), _total, account )

    blance.png

    Attached a sample file in the next one, hopes to help you.

    Best Looks,
    Yingjie Li

    If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-yingjl This worked great!

       

      Thank you so much!