Forum Discussion

joakimfenno's avatar
joakimfenno
Icon for Helper V rankHelper V
3 years ago

help DAX dataset on ssas tabular for reporting services

I need to write a DAX query for a reporting services report

the data source is a ssas tabular cube

the datamodel is kimball dim model

 

requirements

  1. The dataset needs to include columns from one fact table and two related dimension tables
  2. only a few columns from each table shall be included
  3. the dataset shall be filtered from SSRS parameters (both fact and dimension table)

baically what I need is help writing a query that joins the three tables (inner join) as efficient as possible

 

I am elaborating with selectedcolumns, different types of joins (related, naturalimnerjoins, lookup) and filter function

 

I would approciate some input here

 

@Dax help
reporting services
SSAS Tabular

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi joakimfenno ,

    To create a DAX query that joins the three tables (inner join) as efficiently as possible, you can use the following steps:

    1. Start by selecting the columns you need from each table. You can use the SELECTCOLUMNS function to create a table with only the columns you need.

    2. Use the RELATED function to create a relationship between the fact table and the dimension tables. This function returns a related value from another table.

    3. Use the FILTER function to filter the data based on the SSRS parameters. You can use this function to filter both the fact table and the dimension tables.

    4. Use the INNERJOIN function to join the fact table and the dimension tables. This function returns a table that contains only the rows that have matching values in both tables.

    Here is an example of a DAX query that joins a fact table and two dimension tables using the steps above:

    EVALUATE
INNERJOIN(
    SELECTCOLUMNS(FactTable, "Column1", FactTable[Column1], "Column2", FactTable[Column2]),
    SELECTCOLUMNS(DimensionTable1, "Column3", DimensionTable1[Column3], "Column4", DimensionTable1[Column4]),
    SELECTCOLUMNS(DimensionTable2, "Column5", DimensionTable2[Column5], "Column6", DimensionTable2[Column6]),
    RELATED(DimensionTable1[RelatedColumn]),
    RELATED(DimensionTable2[RelatedColumn]),
    FILTER(FactTable, FactTable[Column7] = @Parameter1),
    FILTER(DimensionTable1, DimensionTable1[Column8] = @Parameter2),
    FILTER(DimensionTable2, DimensionTable2[Column9] = @Parameter3)
)

    In this example, FactTable is the name of the fact table, DimensionTable1 and DimensionTable2 are the names of the dimension tables, and Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, and Column9 are the names of the columns you need from each table. @Parameter1, @Parameter2, and @Parameter3 are the names of the SSRS parameters you want to use to filter the data.

     

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • joakimfenno's avatar
      joakimfenno
      Icon for Helper V rankHelper V
      3 years ago

      Thanks for your help

       

      Do you mean INNERJOIN or NATURALINNERJOIN function?

       

      The fact table have relationships to the dimension tables in the SSAS model. RELATED should not be needed?

       

      When using NATURALINNERJOIN I am getting the error below

       

      An incompatible join column, was detected. 'NATURALINNERJOIN' doesn't support joins by using columns with different data types or lineage.