Forum Discussion
Hours Comparison Using USERELATIONSHIP: Unable to Display Trend
Hi,
Please support.
I am doing a comparison of any two time periods using USERELATIONSHIP.
Example:
base_vol = calculate(sum('fact_Rawdata'[total_s]))
comparison_vol = calculate([base_vol],all(dim_Week_base[Year_wk]),USERELATIONSHIP('fact_Rawdata'[Year_wk],dim_Week_comparison[Year_wk]))
Everything works fine when I want to see details in one table for any two scenarios' Hours Impact.
hours_impact = IF([comparison_hrs_at_base_Totals]=0,BLANK(),[comparison_hrs_at_base_Totals]-[base_hrs])
However, when I want to prepare a trend using weeks from the base table, the comparison week shows instead of the weeks chosen in the main filter.
I want to create a trend based on base building and base weeks (for example, comparing 3 weeks to one). Weeks 24, 25, and 26 should remain and show hours impact to week.
Thanks 🙂
HI hello_12345 ,
The main issue is that USERELATIONSHIP can override your base week context, so your trend visuals end up showing the wrong weeks. The best way to solve this is to use TREATAS in your measure. This keeps your X-axis (weeks) correct and maps your base weeks to the comparison weeks for the calculation.
Here’s how you can write your comparison measure:
comparison_vol =
CALCULATE( [base_vol], REMOVEFILTERS(dim_Week_base[Year_wk]),
TREATAS( VALUES(dim_Week_base[Year_wk]),
dim_Week_comparison[Year_wk] ) )Keep your trend visuals aligned to the weeks you select, Show the correct comparison values for each week, And avoid the visual context issues that come up with USERELATIONSHIP.
Hi hello_12345 ,
Yes, TREATAS can be used, but ensure:
TREATAS maps correctly: TREATAS(VALUES(BaseTable[Column]), ComparisonTable[Column]) is right.
Relationships: Underlying relationships (active or inactive with USERELATIONSHIP) are consistent with what TREATAS expects.
Filter Context: REMOVEFILTERS might be too broad; consider ALLEXCEPT or ALLSELECTED if you need to preserve some context.
Your USERELATIONSHIP approach was often simpler for comparisons if the relationships are set up. TREATAS is powerful but can be tricky with complex filter contexts.
Key thing: The base_vol calculation itself needs to be correctly picking up the dim_Week_comparison and dim_b_comparison filters via TREATAS or USERELATIONSHIP. If base_vol is not reacting, it implies the filter context from the comparison tables isn't reaching it.
8 Replies
HI hello_12345 ,
The main issue is that USERELATIONSHIP can override your base week context, so your trend visuals end up showing the wrong weeks. The best way to solve this is to use TREATAS in your measure. This keeps your X-axis (weeks) correct and maps your base weeks to the comparison weeks for the calculation.
Here’s how you can write your comparison measure:
comparison_vol =
CALCULATE( [base_vol], REMOVEFILTERS(dim_Week_base[Year_wk]),
TREATAS( VALUES(dim_Week_base[Year_wk]),
dim_Week_comparison[Year_wk] ) )Keep your trend visuals aligned to the weeks you select, Show the correct comparison values for each week, And avoid the visual context issues that come up with USERELATIONSHIP.
Hi hello_12345,
You're on the right track using USERELATIONSHIP to compare different time periods, but the issue arises because the comparison_vol measure activates a different relationship that overrides your base week's context, leading to the mismatch in visual trends.Recommended Fix:
To retain the base week context for the X-axis while still calculating the comparison correctly, use TREATAS instead of USERELATIONSHIP. This way, you can preserve the base week selection in visuals like trends.
Updated Measure Using TREATAS:
comparison_vol =
CALCULATE(
[base_vol],
REMOVEFILTERS(dim_Week_base[Year_wk]),
TREATAS(
VALUES(dim_Week_base[Year_wk]),
dim_Week_comparison[Year_wk]
)
)Why This Works:
TREATAS maps the base week selection onto the comparison table without breaking the base week context.
Keeps trend visuals aligned to base filters (e.g., 2025-26, 2025-27, 2025-28) while still evaluating the comparison logic.
Hope this helps!Best regards,
Jainesh Poojara
Power BI DeveloperThank you jaineshp and rohit1991
I can see the selected weeks in the matrix table now; however, the hours impact (values) in this matrix table is not changing when selecting any comparison scenario. I'm not sure why this is happening.
Can I use TREATAS in this type of measure?
comparison_vol =
var a = MAX('Switch'[Type])
var weekly = CALCULATE(
[base_vol],
REMOVEFILTERS(dim_Week_base[Year_wk]),
TREATAS(
VALUES(dim_Week_base[Year_wk]),
dim_Week_comparison[Year_wk]
)
)
var building__ = calculate([base_vol],REMOVEFILTERS(dim_b_base[building]),REMOVEFILTERS(dim_Week_base[Year_wk]),
TREATAS(
VALUES(dim_Week_base[Year_wk]),
dim_Week_comparison[Year_wk]
),
TREATAS(
VALUES(dim_b_base[building]),
dim_b_comparison[building])
)var ret = SWITCH(a,
"Weekly Comparison", weekly,
"Building Comparison", building__)RETURN ret
That was my previous measure:comparison_vol =
var a = MAX('Switch'[Type])
var weekly = calculate([base_vol],all(dim_Week_base[Year_wk]),USERELATIONSHIP('fact_Rawdata'[Year_wk],dim_Week_comparison[Year_wk]))
var building__ = calculate([base_vol],all(dim_b_base[building]),all(dim_Week_base[Year_wk]),USERELATIONSHIP('fact_Rawdata'[building],dim_FC_comparison[FC]),USERELATIONSHIP('fact_Rawdata'[Year_wk], dim_Week_comparison[Year_wk]))var ret = SWITCH(a,
"Weekly Comparison", weekly,
"Building Comparison", fc__)RETURN ret
Hi hello_12345 ,
Yes, TREATAS can be used, but ensure:
TREATAS maps correctly: TREATAS(VALUES(BaseTable[Column]), ComparisonTable[Column]) is right.
Relationships: Underlying relationships (active or inactive with USERELATIONSHIP) are consistent with what TREATAS expects.
Filter Context: REMOVEFILTERS might be too broad; consider ALLEXCEPT or ALLSELECTED if you need to preserve some context.
Your USERELATIONSHIP approach was often simpler for comparisons if the relationships are set up. TREATAS is powerful but can be tricky with complex filter contexts.
Key thing: The base_vol calculation itself needs to be correctly picking up the dim_Week_comparison and dim_b_comparison filters via TREATAS or USERELATIONSHIP. If base_vol is not reacting, it implies the filter context from the comparison tables isn't reaching it.
Hi hello_12345
Thank you for reaching out to the Microsoft Fabric Forum Community.
jaineshp rohit1991 Thanks for the inputs. I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.
Hey v-priyankata,
Thank you for the kind recognition - always happy to contribute to our community's success!
Best Regards,
Jainesh Poojara | Power BI DeveloperHi hello_12345
I wanted to check if you had the opportunity to review the information provided by users. Please feel free to contact us if you have any further questions.
Hi hello_12345
Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.
