Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Previous Value from slicer selection

Hi, 

 

Wondering if anyone can help me on this one:

 

I have the following simple measure: 

Total = COUNTROWS('Fleet (Cars)')

In another table we have the labels for year ranges such as:

 

 

RangeIndex
2017-20181
2018-20192

 

When the user clicks the slicer, it shows the number of rows for that period, but I could also like to automatically show the previous period. I have tried with things like the following but to no avail.

 

Previous = CALCULATE(COUNTROWS('Fleet (Cars)');'Ranges'[Index]-1)

Any suggestions how I could achieve this?

 

Many thanks, 

 

Matt

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi again, 

     

    Just to let you know we've managed to figure it out with the following:

     

    var a = CALCULATE(Max('Ranges'[Index])-1)
var b = CALCULATE(
       DISTINCTCOUNT('Fleet (Cars)'[id]);
        filter(
          ALL('Fleet (Cars)');true());'Ranges'[Index]=a;)
return b

    Regards, 

     

    Matt

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hi Anonymous

     

    Give this a shot

     

    Previous =
CALCULATE (
    COUNTROWS ( 'Fleet (Cars)' ),
    FILTER (
        ALL ( 'Ranges'[Index] ),
        'Ranges'[Index]
            = SELECTEDVALUE ( 'Ranges'[Index] ) - 1
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks Zubair_Muhammad but getting a multiple column error on this one:

       

      The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

       

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Hi Anonymous

         

        Could you show me a screen shot? or share the file?

        How are the tables related?