Bring just the needed dimension data to my document

[Bimtng]

Hi all.

I have a dimension that has a great number of rows. My model has a table of facts that does not need all the rows in the dimension table. The question is: How can I bring to my model just the relationed rows between both tables? I take all the facts, however I would like to bring just the dimension rows connected with the facts, not all of them as I do now.

Thanks in advance.

[GilbertQ · Accepted solution]

Hi @Bimtng

What you could do, is in your SQL Query, you could write the TSQL for your Dimension table that it only brings the Dimension ID's where they exist in your Fact Table

EG:

Select * 
from DimensionTable
where DimensionID in
   (
     Select Distinct DimensionID
     from FactTable
  )

That result will be your Dimension table will only bring in the related DimensionID from your Fact table.

[dkay84_PowerBI]

Are you using Direct Query or Import for your data?

[Bimtng]

I do not know if I have understood your question. Both tables are in a SQL Server database.

[dkay84_PowerBI]

When you connected to the SQL database, did you choose to import or do direct query?