Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Range | Index |
2017-2018 | 1 |
2018-2019 | 2 |
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
Solved! Go to Solution.
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
Give this a shot
Previous = CALCULATE ( COUNTROWS ( 'Fleet (Cars)' ), FILTER ( ALL ( 'Ranges'[Index] ), 'Ranges'[Index] = SELECTEDVALUE ( 'Ranges'[Index] ) - 1 ) )
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.
Could you show me a screen shot? or share the file?
How are the tables related?
Relation is a bidireccional one to many between the IDs of the ranges:
@Zubair_Muhammad Thanks for your assistance!
Matt
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
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |