Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Try this calculated column. See file attached
Highest Order = VAR HO = CALCULATE ( MAX ( 'Order'[Amount] ) ) RETURN IF ( ISBLANK ( HO ), 0, HO )
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
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)
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.
Try this calculated column. See file attached
Highest Order = VAR HO = CALCULATE ( MAX ( 'Order'[Amount] ) ) RETURN IF ( ISBLANK ( HO ), 0, HO )
Did you try it as a measure?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |