Forum Discussion

Maximous89's avatar
Maximous89
Helper I
9 months ago
Solved

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 an...
Need Help
  • DataNinja777's avatar
    DataNinja777
    9 months ago

    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,

     

     

Shubham_rai955's avatar
Shubham_rai955
Super User
9 months ago

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