Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have following problem, I need to calculate a specific value, but to do that each time I have to use as a part of calculation a value from the prior row (based on the q-yyyy). I have in a table column from source table [Quarters] and I added one measure to count total rows
Total = CALCULATE ( COUNTROWS ( dimSource ), dimSource[Action] = "OK" )
and now I need a measure that will calculate something extra but using the "total" from previous measure and previous row 🙂
Indx =
VAR _a =
CALCULATE (
MAX ( 'dimSource'[Quarter] ),
FILTER (
ALL('dimSource'),
[Quarter] < MAX ( 'dimSource'[Quarter] )
)
)
VAR _result =
CALCULATE (
MAX ( 'dimSource'[Quarter] ),
FILTER (
ALL ('dimSource'),
[Quarter] = _a
)
)
RETURN
_result
Based on the above I get a formula which returns to me what is the preceding quarter, but I need a total value instead of quater
Solved! Go to Solution.
Try this:
Total =
VAR LastQuarter =
CALCULATE (
MAX ( 'dimSource'[Quarter] ),
FILTER (
ALL('dimSource'),
[Quarter] < MAX ( 'dimSource'[Quarter] )
)
)
VAR StagingTable = FILTER(ALLSELECTED(dimSource),[Action]="OK" && [Quarter] = LastQuarter)
VAR _result = SELECTCOLUMNS(SUMMARIZE(StagingTable,
[Quarter],
"Count",CALCULATE(COUNTROWS (StagingTable))
),
"Total",MIN([Count]))
RETURN
_result
works like a dream, thanks!
Hi @m4xon ,
If I understand the problem, replace the quarter value with the total value, and replace the = with<=
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
VAR _result =
CALCULATE (
MAX ( 'dimSource'[Total] ),
FILTER (
ALL ('dimSource'),
[Quarter] < = _a
)
)
Proud to be a Super User!
Hello,
It would be to easy 😉 We don't have a column [Total], it's a measure. In base table we have only [Quarter], this is the question, how to put a measure into it.
Thanks!
Max
Try this:
Total =
VAR LastQuarter =
CALCULATE (
MAX ( 'dimSource'[Quarter] ),
FILTER (
ALL('dimSource'),
[Quarter] < MAX ( 'dimSource'[Quarter] )
)
)
VAR StagingTable = FILTER(ALLSELECTED(dimSource),[Action]="OK" && [Quarter] = LastQuarter)
VAR _result = SELECTCOLUMNS(SUMMARIZE(StagingTable,
[Quarter],
"Count",CALCULATE(COUNTROWS (StagingTable))
),
"Total",MIN([Count]))
RETURN
_result
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
64 | |
63 |
User | Count |
---|---|
140 | |
105 | |
102 | |
81 | |
67 |