CODEWITHSUNDEEP
powerbi
Neil P
Neil P

Reputation: 3200

power bi - how to manage unrelated dimensions

I'm attempting to create a shared date dimensions between two fact tables in Power BI, based off of a relational data source.

Currently, if I include an unrelated dimension in the report, I get numbers duplicated across multiple rows, where they don't really apply.

I'm wondering if there is any way to tell Power BI that certain dimensions cannot be used with certain fact tables, similar to using IgnoreUnrelatedDimensions in SSAS.

Currently the only solution I can find is to create a separate date dimension, so that the two fact tables have no relationship that could be used to join them, however this would mean forfeiting the ability to do any time based comparisons.

Upvotes: 1

Views: 814

Answers (1)

Ian Bennett
Ian Bennett

Reputation: 123

Create a combined view of the fact tables with only compatible columns to be used for time based comparison:

  1. In Query Editor, create new queries for your fact tables by referencing i.e. right click original query and select "Reference".
  2. Then in those "copies" cut out the incompatible dimensions.
  3. Rename columns to align terminology (e.g. Sales Date ==> Transaction Date, Payment Date ==> Transaction Date).
  4. Use "Merge Queries" function to combine the copies using Full Outer Join.
  5. Join this merged view to your date dimension

Upvotes: 0

Related Questions