Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

@kantoboy

 

Try this calculated column. See file attached

 

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

getmaax.png


Regards
Zubair

Please try my custom visuals

View solution in original post

11 REPLIES 11
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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. 

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) 

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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. 

 

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.

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. 

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?

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

 

 

If you add the index column that should not be the case because each line will have its unique index and upon doubling checking my DAX is still running for the reports I had created this for, that is strange though.

@kantoboy

 

Try this calculated column. See file attached

 

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

getmaax.png


Regards
Zubair

Please try my custom visuals

Did you try it as a measure?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.