Mapping categories to records based on ranges

[erlingsa]

Hi,

I am facing a DAX challenge and would very much appreciate help with this. I have a general ledger table and a mapping table catergorizing the accounts based on ranges. In Excel I probably would use the VLOOKUP-formula to allocate the category to each line of data. I want to do this in Power BI as well.

I have thought of two possible solutions:

1. Create a calculated column in the General ledger table that selects the appropriate category
2. Create a measure that summarizes the values of each category based on the ranges

However, this was not as straightforward as in Excel. I would appreciate any help with how I should go about solving this. I suppose there is a "best practice" method for this kind of problem that I am not aware of.

[amitchandak]

@erlingsa, try option 2 unless it's too complicated to do so

See:https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

https://radacad.com/parsing-organizational-hierarchy-or-chart-of-accounts-in-power-bi-with-parent-ch...

[amitchandak]

@erlingsa , Try Option 2 unless that is too complicated to do that

Refer:https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

https://radacad.com/parsing-organizational-hierarchy-or-chart-of-accounts-in-power-bi-with-parent-ch...

[erlingsa · Accepted solution]

Thank you very much for your response @amitchandak. It was not quite what I was looking for, but I fould an answer by looking a bit further, and post the solutions here in order to help future readers of this post.

Option 1 can be solved with Static segmentation (https://www.daxpatterns.com/static-segmentation/)
Option 2 can be solved with Dynamic segmentation (https://www.daxpatterns.com/dynamic-segmentation/)