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.
I have Table1
OpNr | ApNr | Sum | Date
123 | 456 | 300 | 2019-07-17
123 | 856 | 50 | 2019-08-07
Expected result
ApNr | TotalSumByOpNr | MaxDate
456 | 350 | 2019-08-07
856 | 350 | 2019-08-07
Total | 350 | empty (show no dates)
I use
TotalSumByOpNr = SUMX(VALUES(Table1[OpNr]), CALCULATE(MAX(Table1[Sum])))
MaxDate = MAX(Table1[Date])
Why max return wrong result
Measure = MAX(Table1[Date])
expected 2019-08-07 all
How to dermine dependants by which max function filter values?
Solved! Go to Solution.
Hi, @Analitika
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table1:
You may create two measures as below.
TotalSumByOpNr =
var tab =
SUMMARIZE(
Table1,
Table1[ApNr],
"Result1",
var _opnr = SELECTEDVALUE(Table1[OpNr])
return
CALCULATE(
SUM(Table1[Sum]),
FILTER(
ALL(Table1),
Table1[OpNr]=_opnr
)
)
)
return
MAXX(
tab,
[Result1]
)
MaxDate =
var tab =
SUMMARIZE(
Table1,
Table1[ApNr],
"Result2",
var _opnr = SELECTEDVALUE(Table1[OpNr])
return
CALCULATE(
MAX(Table1[Date]),
FILTER(
ALL(Table1),
Table1[OpNr]=_opnr
)
)
)
return
IF(
ISFILTERED(Table1[ApNr]),
MAXX(
tab,
[Result2]
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Analitika
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table1:
You may create two measures as below.
TotalSumByOpNr =
var tab =
SUMMARIZE(
Table1,
Table1[ApNr],
"Result1",
var _opnr = SELECTEDVALUE(Table1[OpNr])
return
CALCULATE(
SUM(Table1[Sum]),
FILTER(
ALL(Table1),
Table1[OpNr]=_opnr
)
)
)
return
MAXX(
tab,
[Result1]
)
MaxDate =
var tab =
SUMMARIZE(
Table1,
Table1[ApNr],
"Result2",
var _opnr = SELECTEDVALUE(Table1[OpNr])
return
CALCULATE(
MAX(Table1[Date]),
FILTER(
ALL(Table1),
Table1[OpNr]=_opnr
)
)
)
return
IF(
ISFILTERED(Table1[ApNr]),
MAXX(
tab,
[Result2]
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Proud to be a Super User!
MAX is evaluating for each row, if you want to exlcude row context in the formula you can use something like this
_MAX Date = CALCULATE (Max (Table[date]),All(Table))
Proud to be a Super User!
@Analitika , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Max seems to work for Grand total. do you need only one row
Same value in all column
Measure = calculate(MAX(Table1[Date]), allselected(Table1))
or
Measure = calculate(MAX(Table1[Date]), all(Table1))
Appreciate your Kudos.
I have Table1
OpNr | ApNr | Sum | Date
123 | 456 | 300 | 2019-07-17
123 | 856 | 50 | 2019-08-07
Expected result
ApNr | TotalSumByOpNr | MaxDate
456 | 350 | 2019-08-07
856 | 350 | 2019-08-07
Total | 350 | empty (show no dates)
I use
TotalSumByOpNr = SUMX(VALUES(Table1[OpNr]), CALCULATE(MAX(Table1[Sum])))
MaxDate = MAX(Table1[Date])
@Analitika , add these columns with OpNr in your visual
calculate(lastnonblankvalue(Table[Date], Sum(Table[Sum])), allexcept(Table[OpNr]))
calculate(max(Table[Date]), allexcept(Table[OpNr]))
It is not working, as produce dublicates
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |