I want to get an account name from a related table. I understand RELATED needs to use row context and I can get the account balance using RELATED (SUMX…) but how do I get text using RELATED in a measure. In other words, what is the function that uses row context to get text from another table?
Do you have an example? Finding it hard to visualize what you are looking for.
Thanks,
Thank you. In this example, Line_Detail_Account_Ref and ID represent the ID # for the account. Basically, it’s an account number. ACCOUNT is a Chart of Accounts. I am trying to sum up Line Amount but use an account name instead of account number. The NAME field in the ACCOUNT table houses the Account name. I can easily add a column BILL ACCOUNT NUMBER as seen below. But can I do this as a measure?
First of all on this, I’m concerned as to why you even need to do this as I really recommend not having calculated columns in your fact tables. From experience these are hardly ever required.
You want to be using measures for nearly all calculations here. This simplifies things immensely. It’s important though to understand the context of your calculation when working with measures…which is difficult to ascertain here.
Definitely review context in-depth here if this isn’t clear.
Potentially you should just do a merge here inside the query editor if you have to add this dimension to the table.
BUT it doesn’t really make sense to me bringing a column from your lookup table down to your fact table. You want all your filtering to be down from your lookup tables.
Definitely go through this course here if you haven’t already. This goes through all my best practice tips for the modeling area.
Hopefully I’m giving you some idea here, because my thoughts are you just don’t even need to be doing what you are trying to do.
If I’m completely wrong here let me know.
Thanks
Sam
I agree I am trying to not use a column as you suggest in your videos. When I try to do a measure this is what I think I should do but it doesn’t work:
Bill Account Name Measure = related(name) -see image below.
The thing I don’t understand is how do I get the measure to return a name? I can use a measure in this same situation to return a balance by using SUMX. This measure works: BillAccountBalanceMeasure = SUMX(Account, Account[Current_Balance]>1)
But you can see, I am getting a number, not text.
Are you suggesting if I want to associate the account name in one table with the account number in another, I should handle that through data modeling and not measures? Perhaps how I am thinking of measures don’t make sense in this context?
When you are looking to bring in text you need to use the SELECTEDVALUE function.
Have you tried that one.
What the ultimate measure is will depend on the context of the calc though, which I can’t really tell at the moment.
Do you really need a measure here though, that’s what is a bit confusing. Why can’t you just drag the dimension/column onto your report page and go from there?
What is calculating this via a measure actually achieving here because it looks like your model is setup correctly with the right relationship type.
Remember to let the data model do all the hard work here around the filtering of your data and results.
Sam
Ah, SELECTEDVALUE. I wasn’t aware of that one. That works. I will also try your other suggestion of just dragging it to the dimension page. And I will look at the modeling videos you suggested to see what works best. Thank you for helping here and thank you for the great site.
Update. I got it to work by changing how I modeled the data. Thank you for your assistance and pointing me in the right direction.