cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kantoboy
New Member

getting a value from a column in a related table

Newbie trying to get a hang of DAX. I have a table with a one to many relationship with another table, I use relatedtable function to get all the values related to the current row. But I can't figure out how to extract the value of a specific column in that returned table. So for example if a customer has multiple orders. I would like to get the date of the last order. Is this the right use of relatedtable?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: getting a value from a column in a related table

@kantoboy

 

Try this calculated column. See file attached

 

Highest Order =
VAR HO =
    CALCULATE ( MAX ( 'Order'[Amount] ) )
RETURN
    IF ( ISBLANK ( HO ), 0, HO )

getmaax.png

Try my new Power BI game Cross the River

View solution in original post

11 REPLIES 11
Super User IV
Super User IV

Re: getting a value from a column in a related table

You should be able to get there via that route or perhaps look at LOOKUPVALUE function. Specific answer depends on specific situation and data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


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

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

kantoboy
New Member

Re: getting a value from a column in a related table

Table 1 (customer)

 

ID

Name

123

John

566

Jeff

344

George

 

Table 2 (order)

 

OrderID

ID

Amount

1

123

100

2

123

150

3

123

3

4

566

60

 

I want to create a column in table 1 that shows the largest amount for each customer or return zero if a customer has no orders. I tried using lookup but it errors out as the result returns more than one row for customer 123. Hope that makes sense. 

Super User IV
Super User IV

Re: getting a value from a column in a related table

In that case, assuming an relationship on ID all you need is a table visualization with Name column and Amount column and pick the MAX aggregation. Click the drop down on Name and choose "Show items with no data".

 

If you are truly itching to write some DAX as a measure, you could do this:

 

Measure = 
VAR __Max = MAX(LKOrders[Amount])
RETURN
IF(ISBLANK(__Max),0,__Max) 

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

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

kantoboy
New Member

Re: getting a value from a column in a related table

Hi Greg,

 

Thanks for the quick reply. Assuming I need to do this via dax instead of a table visualization, is this not possible? I'm trying to get my head around using DAX instead of SQL to solve problems. 

 

For example in T-SQL I would do a select top 1 <column_name>, join <related tables>, and then order by <column_name>. easy peasy. 

 

nirvana_moksh Post Prodigy
Post Prodigy

Re: getting a value from a column in a related table

I have something like the below DAX after on my related table I added a Index Column and then I just wanted to bring the latest comment for each item (since one item can have multiple comments). 

 

CALCULATE(LASTNONBLANK('TABLE A[Column],1),FILTER(TABLE A','TABLE A'[Index] = MAX('TABLE A'[Index])))

 

If there is a relationship between the two tables this will work fine. Give it a shot.

Highlighted
kantoboy
New Member

Re: getting a value from a column in a related table

Hi Nirvana,

 

Thanks for the reply, I can't quite get your code to work. It just returns the max value of a column, regardless of customer id.  When I do relatedtable(table2), I know my relationship between the two tables works works because I can concantenatex the right value. However, I can't figure out how to extract the value of one column directly. 

nirvana_moksh Post Prodigy
Post Prodigy

Re: getting a value from a column in a related table

Did you try it as a measure?

nirvana_moksh Post Prodigy
Post Prodigy

Re: getting a value from a column in a related table

And when you mean the value of the 'Column', do you mean if a RowID 2 has 5 values in Column X, to return all those 5 values? Or do you want to see the value of Column X corresponding to individual line items?

kantoboy
New Member

Re: getting a value from a column in a related table

so using my two tables your code will return the max value of column "amount" ie 150, regardless of which customer id 150 was associated with.

So I only want the dax to return one value . IE 150 for customer id 123 (line 1 of customer table) and 60 for customer id 566 (line 2 of customer table and 0 for customer 344 (line 3 of customer table. My desired calculated column / measure example below.

 

ID

Name

Highest order

123

John

150

566

Jeff

6

344

George

0

 

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors