Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi Everyone,
I need help with my task. I need to replicate a Power BI join in SSRS (Report Builder). In Power BI, there are 2 DAX queried tables. One table for transactions, and one for a master data. I need to show the Store IDs from Transaction table that
doesn't have an "Equivalent hierarchy" in the Master Data table.
Transaction Table | |
Store ID | Amt |
A | 200 |
B | 100 |
C | 50 |
Master Data Table
Store ID | Equivalent Hierarchy |
A | HIER |
C | HIER |
RESULT | |
B | 100 |
In Power BI, I use Merge to get the Result table, and it works fine. However when I try to replicate it to DAX query for my SSRS report, it shows a different result. It shows more rows than in Power BI.
Below is my DAX query for my dataset in SSRS:
EVALUATE
NATURALLEFTOUTERJOIN
(
SUMMARIZECOLUMNS ( 'Store Inv'[Store Id],
'Store Inv'[Store Description],
FILTER (
'Date',
'Date'[Year]
>= YEAR ( NOW () ) - 1
),
FILTER (
'Area',
'Area'[Code] = "AC"
),
"Gross Amt", [Net Amount - Invoiced],
"Gross Returns Amt", [Total Returns Amount]
)
,
SUMMARIZECOLUMNS ( 'Store Inv'[Store Id],
'Store Hierarchy'[Hierarchy Name],
FILTER (
'Date',
'Date'[Year]
>= YEAR ( NOW () ) - 1
),
FILTER (
'Area',
'Area'[Code] = "AC"
),
FILTER (
'Store Hierarchy',
'Store Hierarchy'[Hierarchy Name] = "HIER"
),
"Qty",[Net Quantity - Invoiced (EA)]
)
)
I think there's a problem with my NATURALLEFTOUTERJOIN. Is there any other functions I can use to merge 2 tables in a query?
Thank you very much.
Best Regards,
Ian
Hi @Anonymous ,
To what I can understand you want to return all the lines that don't have the HIER value on master data correct?
Be aware that the NATURALLEFTOUTERJOIN performs an inner join of a table with another table. The tables are joined on common columns (by name) in the two tables so it returns the common values.
On the query editor you can choose if all the lines from the left or the right side are present and then filter out the HIER column. (believe is what you are doing).
Try the following code:
Table =
FILTER (
SUMMARIZE (
'Transaction';
'Transaction'[Store ID];
"Sales"; SUM ( 'Transaction'[Amt] );
"HIERCOLUMN"; LOOKUPVALUE (
'Master Data'[Equivalent Hierarchy];
'Master Data'[Store ID]; 'Transaction'[Store ID]
)
);
[HIERCOLUMN] <> "HIER"
)
You can add aditional columns to your summmarize and replace the names by the correct ones on your model.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |