Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX Query Builder

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 ...
MFelix's avatar
MFelix
Super User
6 years ago

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.