Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I need 2 measures that use 1 of 2 measures in a fact table, the measure selected is to be based on the two joins with the dimension table.
The Fact table has 2 Product codes (Product 1 and Product 2) and 2 measures -Measure1 and Measure 2.
I have a Product dimension with a Product code.
Join 1 = Dim.Productcode = Fact.Productcode_1 (active relationship)
Join 2 = Dim.Productcode = Fact.Productcode_2 (inactive relationship).
My new measures
ProductMeasure1 needs to be to use join 1 and sum(Measure1)
ProductMeasure2 needs to be to use join 2 and sum(Measure2) .
ProductMeasure1 seems to work:-
ProductMeasure1 = SUMX(FILTER( 'FactTable'
, RELATED('DimTable'[Productcode])
= FactTable[Fact.Productcode_1]
)
,FactTable[Measure1])
ProductMeasure2 doess not work because of the USERELATIONSHIP, probably not place or construction:-
ProductMeasure1 = SUMX(FILTER( 'FactTable'
, RELATED('DimTable'[Productcode])
I need to use this relationship : USERELATIONSHIP('FactTable'[Productcode2],'DimTable'[Productcode])
= FactTable[Fact.Productcode_2]
)
,FactTable[Measure2])
Any ideas or other solutions ?
Thanks in advance
Solved! Go to Solution.
Thanks for your fast reply and solution. I will try them and share my findings
@KeenEye , it should be like
For join 1 , you need a measure like // Dim.Productcode = Fact.Productcode_1 (active relationship)
calculate(Sum(FactTable[Measure1]) )
for join 2
calculate(Sum(FactTable[Measure1]),USERELATIONSHIP('FactTable'[Productcode2],'DimTable'[Productcode]) )
refer
Thanks, it works and is much simpler than I thought.
Great support 👍 😃
Thanks for your fast reply and solution. I will try them and share my findings
User | Count |
---|---|
68 | |
47 | |
21 | |
19 | |
15 |
User | Count |
---|---|
123 | |
42 | |
39 | |
26 | |
24 |