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 ...
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.

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        mahoneypat Each measure includes a beginning balance for each card/account since none of them actually started at zero at 1/1/20, when I'd like to begin the reporting.

        Also the individual measures allow me to create individual lines on the graph for each account. The measure you provided below does allow me to select multiple accounts, but when multiple are selected, it combines them into a single line. I'd like to be able to see two (or more) account balances displayed separately.

        Thank you for your help!