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.
Hello community!
Is anyone familiar with writing the Dax Programming to find the nearest (closest ) value in the table?
For example, My table is like this:
And I want to find the nearest Model built time value with 1 month average built time (in this case, today date is 2022/11/16 so
(213 + 444 + 532 + 111 / 4 ) = 325 )
so the nearest value of 325 in this table should be 213 (A125) not 345 (A124) as that model not in a past month from current date.
Therefore, how do i return 213 using the dax programming?
Thank you so much for your help!
Solved! Go to Solution.
Hi, @heejinyune
You can try the following methods.
Column:
Month = MONTH([Model built Date])
Measure:
Nearest Average =
CALCULATE(AVERAGE('Table'[Model built time]),FILTER(ALL('Table'),[Month]=MONTH(TODAY())))
Difference = ABS(SUM('Table'[Model built time])-[Nearest Average])
Nearest Value =
Var _mindiff=MINX(FILTER(ALL('Table'),[Month]=MONTH(TODAY())),[Difference])
Return
IF([Difference]=_mindiff,SUM('Table'[Model built time]))
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @heejinyune
You can try the following methods.
Column:
Month = MONTH([Model built Date])
Measure:
Nearest Average =
CALCULATE(AVERAGE('Table'[Model built time]),FILTER(ALL('Table'),[Month]=MONTH(TODAY())))
Difference = ABS(SUM('Table'[Model built time])-[Nearest Average])
Nearest Value =
Var _mindiff=MINX(FILTER(ALL('Table'),[Month]=MONTH(TODAY())),[Difference])
Return
IF([Difference]=_mindiff,SUM('Table'[Model built time]))
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |