cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Frequent Visitor

## DAX formula which returns the text value that related to the range from another table

Hi Community.
I am looking to DAX formula that returns the text value that related to the range from another table.

I have 1st table with wll company accounts in it, 2nd table with accounts ranges (Start account, End account) and related indicator to the range. I need to bring indicator name from 2nd table to the 1st table.

This if formula does not work for account numbers "in between" the range or simply if account # from the 1st table does not match to the Start Account # from the 2nd table.

You can load pbix test file

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Senior Member

## Re: DAX formula which returns the text value that related to the range from another table

First off, thanks for including the PBIX file, that really helped as I couldn't fully understand what you were trying to achieve by your question! 🙂

You don't need a relationship between the tables. I hope I understood your question correctly. My understanding is that you want to have a calculated column in table "Account" which reflects which indicator belongs to the account number. Indicators are given in the "Indicator" column with a start value and end value, and when an account number in the "account" table is equal to start or end or is in between that value, you want that corresponding Indicator to the account table.

This is the dax that does just that:

``Indicator = CALCULATE(SELECTEDVALUE(Indicator[Indicator]), FILTER(ALL(Indicator), Indicator[StartAccount] <= Account[Account] && Indicator[EndAccount] >= Account[Account]))``

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

2 REPLIES 2
Senior Member

## Re: DAX formula which returns the text value that related to the range from another table

First off, thanks for including the PBIX file, that really helped as I couldn't fully understand what you were trying to achieve by your question! 🙂

You don't need a relationship between the tables. I hope I understood your question correctly. My understanding is that you want to have a calculated column in table "Account" which reflects which indicator belongs to the account number. Indicators are given in the "Indicator" column with a start value and end value, and when an account number in the "account" table is equal to start or end or is in between that value, you want that corresponding Indicator to the account table.

This is the dax that does just that:

``Indicator = CALCULATE(SELECTEDVALUE(Indicator[Indicator]), FILTER(ALL(Indicator), Indicator[StartAccount] <= Account[Account] && Indicator[EndAccount] >= Account[Account]))``

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Frequent Visitor

## Re: DAX formula which returns the text value that related to the range from another table

The formula works perfectly!

Thank you @djerro123

## Helpful resources

Announcements

#### New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)