You’re offline. This is a read only version of the page.
Announcements
9
I am facing an issue with a DAX query while using the "Run a query against a (Power BI) dataset" action in Power Automate. My dataset contains two tables, let's call them TableA and TableB, which are connected through a Many-to-Many relationship. The relationship is based on a key named ParentID in TableA and ItemID in TableB. I need help in writing a DAX query that returns a summarized version of these tables with specific columns, including those with empty or missing values. So that I can then filter these. Either directly in the query on for example TenantID =1 or after the query in a new action.
Here's a more detailed version of my tables:
TableA:
ParentID-TenantID-Quantity-Field1-Field2
| 1 | A | 10 | ||
| 2 | A | 20 | Value1 | Value2 |
| 3 | B | 30 | Value3 |
TableB:
ItemID-TenantID-Carrier-StorageLocation-Field3
| 1 | A | C1 | L1 | Value4 |
| 2 | A | C1 | L1 | Value5 |
| 3 | B | C2 | L2 |
I need to write a DAX query that returns the following result:
ParentID-TenantID-Quantity-Field1-Field2-Carrier-StorageLocation-Field3
| 1 | A | 10 | C1 | L1 | Value4 | ||
| 2 | A | 20 | Value1 | Value2 | C1 | L1 | Value5 |
| 3 | B | 30 | Value3 | C2 | L2 |
I tried the following DAX query, but it didn't work as expected:
EVALUATE
SUMMARIZECOLUMNS(
'TableA'[ParentID],
'TableA'[TenantID],
'TableA'[Quantity],
'TableA'[Field1],
'TableA'[Field2],
"Carrier", MAX('TableB'[Carrier]),
"StorageLocation", MAX('TableB'[StorageLocation]),
"Field3", MAX('TableB'[Field3])
)
This was the only solution that really outputs the contents from the other table without error, but the assignment to the respective items is no longer correct!
I also tried using the RELATEDTABLE function, but it resulted in an error.
1. What is wrong with my DAX query, and how can I fix it to handle empty or missing values?
2. What is the correct way to use the RELATEDTABLE function in this scenario, considering the different key names (ParentID and ItemID) for the Many-to-Many relationship?
3. Is there an alternative way to achieve the desired result using DAX or any other approach within Power Automate, given the constraints of the relationship and the presence of empty fields?
I appreciate any assistance you can provide. Thank you in advance!
Best regards,
Patrick
I got the solution, the Filter is optional but it works:
EVALUATE
FILTER(
SUMMARIZECOLUMNS(
'TableA'[ParentID],
'TableA'[TenantID],
'TableA'[Quantity],
'TableA'[Field1],
'TableA'[Field2],
"Carrier", MAXX(RELATEDTABLE('TableB'), 'TableB'[Carrier]),
"StorageLocation", MAXX(RELATEDTABLE('TableB'), 'TableB'[StorageLocation]),
"Field3", MAXX(RELATEDTABLE('TableB'), 'TableB'[Field3])
),
'TableA'[TenantID] = "@{triggerBody()['text']}"
)
Share
Report
All responses (
Answers (
