cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Maryna Frequent Visitor
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

https://drive.google.com/drive/folders/1UmYcl7frXv347k1nOs4lVxvvW2QeaHxOCapture3.jpgCapture1.JPGCapture2.jpg

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
djerro123 Senior Member
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! 🙂

View solution in original post

2 REPLIES 2
djerro123 Senior Member
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! 🙂

View solution in original post

Maryna Frequent Visitor
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

New Topics Started Badges Coming

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

MBAS 2020

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

Difinity Conference

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

Top Solution Authors
Top Kudoed Authors (Last 30 Days)