Calculate Max value in a table visual

[Vikki]

Hi Everyone,

I am trying to write a DAX measure to calculate Max value in a table visual. Please refer to the image below:

I created a 3-column table visual, column 1 and 3 are dragged from 'AD' table, column 3 [% on Site] is a measure.

Column 2 [Date] is dragged from 'All Entries' table.

I try to calculate Max % on site of each Branch. The result should look like this:

USS  54.55%

CFM  27.58%

DMA 27.27%

...

Could someone help me with the DAX expression?

Thank you,

Vikki

[v-yiruan-msft]

Hi @Vikki ,

Is there any relationship created between table AD and All Entries? Could you please provide some sample data in table All Entries? From your screenshot, it looks like there are multiple dates for each branch, do you want the final result to get the [% on Site] of the latest date for each branch? For example, Branch: USS only shows Date Sep 17, will the final result be what is circled in red in the table below?

Expected result??

Best Regards

[Vikki]

Hi @v-yiruan-msft @amitchandak ,

Thank you for looking into this. 

One of the other problem I am facing is there is many to many relationship between AD table and All Entries table, through people's names, which is not a reliable key. But for now, the client didn't provide anything else.

I wonder if this is not acheivable unless I have a unique key and 1 to many relationship between AD and All Entries table.

Thanks anyway,

Vikki

[aj1973]

Hi @Vikki 

Add a new Table containing distinct values from column Name then link all three together. However this won't solve the view in your Table Visual unless you take only the Max Date from every Branch.

[amitchandak]

@Vikki , Try a new measure like

var _max = calculate(MaxX(summarize(Table, Table[Branch], Table[site], "_1",[Max %]), [_1]), filter(allselected(Table), Table[Branch] = max(Table[Branch]) && Table[site] = max(Table[site])))
return
if( [Max %] = _max, [Max %], blank())