Slicer with Measures - Select Multiple

[Anonymous]

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?

[v-yingjl · Accepted solution]

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 )

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]

@v-yingjl This worked great!

Thank you so much!

[mahoneypat]

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]

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

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]

@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!

[mahoneypat]

Which visual are you using? You should be able to use your account column as the legend to see separate lines.

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

Regards,

Pat