The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
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
Hi @joakimfenno ,
To create a DAX query that joins the three tables (inner join) as efficiently as possible, you can use the following steps:
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.
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.
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.
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.
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.
User | Count |
---|---|
28 | |
10 | |
8 | |
6 | |
5 |
User | Count |
---|---|
33 | |
13 | |
12 | |
9 | |
7 |