The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
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
Hi @Anonymous
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.
Hi @Anonymous
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 |
---|---|
65 | |
61 | |
60 | |
53 | |
27 |
User | Count |
---|---|
181 | |
88 | |
70 | |
48 | |
46 |