Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RandyPgh
Resolver III
Resolver III

Dimension Driving Dimension Selection through Fact

A single fact table attaches to two conformed dimensions. Dimension_1 is actively attached. Dimension_2 is inactively attached. For this fact table only, the goal is for Dimension_2 to filter the list of Dimension_1 as associated to the Fact table, but then to disappear from the calculation, allowing only related Dimension_1 records to affect the results. Another way to look at the goal is that Dimension_2 chooses the Dimension_1 values, but then all Fact records associated with Dimension_1 are calculated, ignoring the Dimension_2 choice. Note that other dimensions might be affecting the results, but these describe the interactions of these two dimensions.

 

For example, I have tried the following DAX that does not accomplish the goal. What does get returned is filtered by both Dim1 and Dim2.

 

VAR __dim1_list =
CALCULATETABLE(
        VALUES('Fact'[Dim1Key]),
        USERELATIONSHIP('Fact'[Dim2Key],'Dim2'[Dim2Key])
)
VAR __result =
    CALCULATE(
        COUNTROWS('Fact'),
        __dim1_list
    )
 
I am obviously not understanding something about the processing of the contexts, but not sure what.
1 ACCEPTED SOLUTION
RandyPgh
Resolver III
Resolver III

It was solved with a bridge table between Dim2 and the Fact table. The bridge contains the appropriate Dim1 and Dim2 pairings, with a relationship {Dim2.Dim2Key, Bridge.Dim2Key} one-to-many join and a {Bridge.Dim1Key, Fact.Dim1Key} many-to-many join. Both joins are uni-directional toward the fact table.

 

Thanks for your suggestions @amitchandak .

 

View solution in original post

2 REPLIES 2
RandyPgh
Resolver III
Resolver III

It was solved with a bridge table between Dim2 and the Fact table. The bridge contains the appropriate Dim1 and Dim2 pairings, with a relationship {Dim2.Dim2Key, Bridge.Dim2Key} one-to-many join and a {Bridge.Dim1Key, Fact.Dim1Key} many-to-many join. Both joins are uni-directional toward the fact table.

 

Thanks for your suggestions @amitchandak .

 

amitchandak
Super User
Super User

@RandyPgh , Try one of the two ways

 

CALCULATE(
COUNTROWS('Fact'),
USERELATIONSHIP('Fact'[Dim2Key],'Dim2'[Dim2Key])
)

or


CALCULATE(
COUNTROWS(summarize('Fact', 'Fact'[Dim1Key])),
USERELATIONSHIP('Fact'[Dim2Key],'Dim2'[Dim2Key])
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors