DAX Query Builder

[Anonymous]

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

[MFelix]

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.