Forum Discussion
TREATAS
Hello sharks,
i have tow columns inside one table (HIER[LEVEL_3_DESC],HIER[LEVEL_5_DESC]), they both have same valus as data points which is manufacturer name but with diffrent filter of H1 and H3, they gave diffrent values,
i have HIER[LEVEL_5_DESC] in my visual and i filter H1, but when i filter H3 i want HIER[LEVEL_5_DESC] to act as HIER[LEVEL_3_DESC] and return those values.
VAR _sum_of_col_H1_MANUFACTURER =
CALCULATE(
[Avg of HANDNP],
REMOVEFILTERS(HIER)
HIER[AC_HIERARCHY_CODE] = "H3",
HIER[HLEVEL] = "MANUFACTURER",
-- The KEY part:
-- Take LEVEL_5_DESC selected in the visual
-- Apply them to LEVEL_3_DESC
TREATAS(
VALUES(HIER[LEVEL_5_DESC]),
HIER[LEVEL_3_DESC]
)
Hi Maximous89 ,
You can use use treatas to achieve your required output.
Since LEVEL_5_DESC (from H1) contains the values you want to map to LEVEL_3_DESC (in H3), treatas is the function which can move that lineage.
Here is the refined measure.
Tested Solution = VAR _SelectedLevel5 = VALUES(HIER[LEVEL_5_DESC]) RETURN CALCULATE( [Avg of HANDNP], REMOVEFILTERS(HIER), -- Remove existing context from the visual HIER[AC_HIERARCHY_CODE] = "H3", -- Force the calculation to look at H3 HIER[HLEVEL] = "MANUFACTURER", -- Ensure we are looking at the right level type TREATAS( _SelectedLevel5, -- Take the selection from the visual (e.g., "Toyota" from Level 5) HIER[LEVEL_3_DESC] -- Apply it as a filter to Level 3 ) )The resultant output is as shown below:
I have attached an example pbix file.
Best regards,
5 Replies
Hi Maximous89 ,
You can use use treatas to achieve your required output.
Since LEVEL_5_DESC (from H1) contains the values you want to map to LEVEL_3_DESC (in H3), treatas is the function which can move that lineage.
Here is the refined measure.
Tested Solution = VAR _SelectedLevel5 = VALUES(HIER[LEVEL_5_DESC]) RETURN CALCULATE( [Avg of HANDNP], REMOVEFILTERS(HIER), -- Remove existing context from the visual HIER[AC_HIERARCHY_CODE] = "H3", -- Force the calculation to look at H3 HIER[HLEVEL] = "MANUFACTURER", -- Ensure we are looking at the right level type TREATAS( _SelectedLevel5, -- Take the selection from the visual (e.g., "Toyota" from Level 5) HIER[LEVEL_3_DESC] -- Apply it as a filter to Level 3 ) )The resultant output is as shown below:
I have attached an example pbix file.
Best regards,
thank you so mush you are right, but in aim to maintain filers i need to add keepfilters like this
CALCULATE(
[Avg of HANDNP],
REMOVEFILTERS( HIER ),
HIER[AC_HIERARCHY_CODE] = "H3",
HIER[HLEVEL] = "MANUFACTURER",-- Map LEVEL_5_DESC → LEVEL_3_DESC
TREATAS( VALUES( HIER[LEVEL_5_DESC] ), HIER[LEVEL_3_DESC] ),-- Preserve existing filter on LEVEL_1_DESC
KEEPFILTERS( VALUES( HIER[LEVEL_1_DESC] ) )
)
You can use TREATAS in DAX to make one column’s values act as a filter on another column—just as you want for HIER[LEVEL_5_DESC] and HIER[LEVEL_3_DESC].
Try rewriting your measure like this:
VAR Selected_Level_5 = VALUES(HIER[LEVEL_5_DESC]) VAR H1_Filter = CALCULATE( [Avg of HANDNP], HIER[AC_HIERARCHY_CODE] = "H1" ) VAR H3_Filter = CALCULATE( [Avg of HANDNP], HIER[AC_HIERARCHY_CODE] = "H3", TREATAS(Selected_Level_5, HIER[LEVEL_3_DESC]) ) RETURN IF( SELECTEDVALUE(HIER[AC_HIERARCHY_CODE]) = "H3", H3_Filter, H1_Filter )This pattern uses TREATAS to map selected LEVEL_5_DESC values onto LEVEL_3_DESC when H3 is filtered.
Change [Avg of HANDNP] with your actual calculation as needed.
Put this measure in your visual. When you filter H3, LEVEL_5_DESC works like LEVEL_3_DESC.
This lets you dynamically swap filters between columns based on your hierarchy filter
Hi,
Share some data to work with, explain the question and show the expected result. Share data in a format that can be pasted in an MS Excel file.
Hi Maximous89 ,
Thank you for reaching out to the Microsoft Fabric Community Forum.
Could you please let us know if the issue has been resolved? I wanted to check if you had the opportunity to review the information provided by DataNinja777 and Shubham_rai955 . If you still require support, please let us know, we are happy to assist you.
Thank you.
