Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
@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))
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 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 🙂
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!!
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
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |