Forum Discussion
Select data based on range values from a realated table
Hi everybody.
I have a summarized table where it is calculated a Score for a project. This table is related to a 'Scale' table where I have several ranges for identify the grade based on the score that is calculated. How can this be completed?
An example of the columns and info is
| Project | Score | ScaleID | ScaleResult |
| Project number 1 - Measure 2020 | 60 | 2020 | B |
| Project number 1 - Measure 2019 | 40 | 2019 | A |
Scales table
| ScaleID | From | To | Scale Result |
| 2020 | 1 | 50 | A |
| 2020 | 51 | 100 | B |
| 2019 | 1 | 40 | A |
| 2019 | 41 | 70 | B |
| 2019 | 71 | 100 | C |
Thanks,
Angel
BTI_Afuentes - This same basic problem has been cropping up in the forums of late so I posted this solution to the Quick Measures gallery. It includes a PBIX of the solution:
https://community.powerbi.com/t5/Quick-Measures-Gallery/LOOKUPVALUE-Range/m-p/974201#M430
3 Replies
Perhaps:
Scale Result Column = VAR __Score = 'Project'[Score] VAR __ID = 'Project'[ScaleID] VAR __Scale = MAXX( FILTER( 'Scales', 'Scales'[ScaleID] = __ID && 'Scales'[From] <= __Score && 'Scales'[To] >= __Score ), [Scale Result] ) RETURN __ScaleBTI_Afuentes - This same basic problem has been cropping up in the forums of late so I posted this solution to the Quick Measures gallery. It includes a PBIX of the solution:
https://community.powerbi.com/t5/Quick-Measures-Gallery/LOOKUPVALUE-Range/m-p/974201#M430
Thank you very much Gerg!
It worked when I used the DAX from the solution on the Quick Measures gallery.
Regards,
Angel
