transforming line context into measure context

[Anonymous]

Hey guys!

I need a conditional column to have a measurement context, because I'm using it as a filter. Below I put some images and codes

Conditional column result:

conditional column = 
var indice = CALCULATE(sum('base'[metric1]) / sum('base'[metric2]),  ALLEXCEPT( 'base',
        'base'[category], 
        'base'[description], 
        'base'[key_values_ogn], 
        'base'[name], 
        'base'[origem], 
        'base'[sub_category], 
        'base'[type], 
        'base'[type_1],
        'base'[date]))
var result = 
IF(indice <0.0021, "Low",
    IF(indice >= 0.0021 && indice <0.0051, "Medium",
        IF(indice >= 0.0051, "High",
            "Others")))
return result

Measure result:

conditional column = 
var indice = CALCULATE(sum('base'[metric1]) / sum('base'[metric2]))
var result = 
IF(indice <0.0021, "Low",
    IF(indice >= 0.0021 && indice <0.0051, "Medium",
        IF(indice >= 0.0051, "High",
            "Others")))
return result

I need the same behavior of the measurement in the column to be able to use it as a filter, can you help me, please?

[MFelix · Accepted solution]

Hi @Anonymous ,

When you refer that you need to have the same behaviour on the column has you have in the measure do you mean you want the calculation to be dinamic based on context of the table but being abble to use it in a slicer?

If you want to use the values of the measure in a slicer create  an unrelated table with results of the measure:

Option

Low

Medium

High

Low

Now create the following measure:

Filtering = IF([Measure] in VALUES('Options'[Option]), 1)

Now add this measure to the filter has a filtering and choose all values thar aren't blank final result below and in attach file:

another thing is that instead of using nested IF you should use switch syntax:

conditional column =
VAR indice =
    CALCULATE ( SUM ( 'base'[metric1] ) / SUM ( 'base'[metric2] ) )
VAR result =
    SWITCH (
        TRUE (),
        indice < 0.0021, "Low",
        indice >= 0.0021
            && indice < 0.0051, "Medium",
        indice >= 0.0051, "High",
        "Others"
    )
RETURN
    result

[Anonymous]

Hey @MFelix !

This is exactly what i needed.

Thank you for helping me! 

Regards

Alex Silva