Multiple Output with Slicer Value

[Vipulgoel]

Hi All,

I have a table with 3 columns in it ( Year, Job ID, Amount). I want to use the year column in the slicer value and upon selecting anything from that slicer the maximum of the Job ID and the Amount corresponding to it should be given as output.

Input Table

Output as follows:

Any help will be highly appreciated, thanks.

[SpartaBI]

@Vipulgoel please try:

Max Job ID = 
VAR _Year = selectedvalue('table'[year])
RETURN
Calculate(max('table'[Job ID]),filter(all('table'),'table'[Year]=_Year))


Max Amount =
VAR _Year = selectedvalue('table'[year]) 
VAR _JobID = Calculate(max('table'[Job ID]),filter(all('table'),'table'[Year]=_Year))
RETURN
Calculate(max('table'[Amount]),filter(all('table'),'table'[Job ID]=_JobID) && 'table'[Year]=_Year))

      

Showcase Report – Contoso By SpartaBI

[Anonymous]

Hi @SpartaBI ,

I have a similar request but is that possible to attain the same in calculated columns instead of measure.

Any help will be appreciated.

Regards

Vipul

[Vipulgoel · Accepted solution]

Hi @SpartaBI 

That solution worked, thanks a lot 🙂

Regards

Vipul

[SpartaBI]

@Vipulgoel hahaha my pleasure 🙂
Please don't forget to accept the previous message as a solution for community visibility and check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. 
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

[VahidDM]

Hi @Vipulgoel 

You can add those 2 columns to the table or other visuals and set the aggregation to MAX or you need two measures to find the max Job ID and Related Amount based on the selected date:

Max Job ID = 
Var _Year = selectedvalue('table'[year])
Return
Calculate(max('table'[Job ID]),filter(all('table'),'table'[Year]=_Year))


Max Amount = 
Calculate(max('table'[Job ID]),filter(all('table'),'table'[Job ID]=[Max Job ID]))

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Vipulgoel]

Hi @VahidDM

With this solution I am getting correct value for Max Job Id Measure based on the slicer selection but the value for Max Amount Measure is not coming correct. The Max Amount Measure is just returning the Maximum value in the Job ID column.

Regards

Vipul