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

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

kantoboy Frequent Visitor
Frequent Visitor

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) 

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

kantoboy Frequent Visitor
Frequent Visitor

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 Established Member
Established Member

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.

kantoboy Frequent Visitor
Frequent Visitor

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 Established Member
Established Member

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

Did you try it as a measure?

nirvana_moksh Established Member
Established Member

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

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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors