Forum Discussion
Solved
Retrieve single column value based on calculation on another column
Hello, This is my Power Bi table where the column "Calls" is calculated column ( a kind of counter). I want to get Local Start Hour when the highest number of Calls were started for a selected na...
Perhaps:
Measure 3 = VAR __Name = MAX('Table7'[Name]) VAR __Table = SUMMARIZE('Table7',[Name],[Local Start Hour],"__Calls",SUM([Calls])) VAR __Max = MAXX(FILTER(__Table,[Name] = __Name),[__Calls]) RETURN MINX(FILTER(__Table,[Name] = __Name && [__Calls] = __Max),[Local Start Hour])Page 5, Table 7
Please follow the steps.
Step 1: Group by your table as below.
Create a DAX measure as follows.
Measure = CALCULATE(MAX('Table'[Local Start Hour]), FILTER('Table','Table'[Sum of Calls]=MAX('Table'[Sum of Calls])))
If this helps, mark it as a solution.
Kudos are nice too.
lazzarjovvch74 Although Greg_Deckler has already provided a solution, sharing another thought on this
Add following measure
Max hour =
CALCULATE(
MAX ( HR[Local Start Hour] ),
TOPN( 1, ALLSELECTED( HR[Local Start Hour] ), [Call], DESC )
)
parry2k thank you for submitting your solution.
At the end of your code, I couldn't use just "Calls", but I had to use an aggregation function. Just to remind you that "Calls" is calculated column, not measure. But the code below doesn't retrieve the correct result yet.
Max hour = CALCULATE(MAX(Table1[Local Start Hour]), TOPN(1, ALLSELECTED(Table1[Local Start Hour]), SUM(Table1[Calls]), DESC))lazzarjovvch74 sorry I missed to mention that Call is a measure
Call = SUM ( HR[Calls] ) Max Hour = CALCULATE( MAX ( HR[Local Start Hour] ), TOPN( 1, ALLSELECTED( HR[Local Start Hour] ), [Call], DESC ) )
