TREATAS

[Maximous89]

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]
)

[v-tsaipranay]

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.

[Ashish_Mathur]

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.

[Shubham_rai955]

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

[DataNinja777 · Accepted solution]

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,

[Maximous89]

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] ) )
)