Measure value is not right

[ngadiez]

I have a table with 2 columns [Duration] and [Duration within] both have value 1,2,3,4.

I also have a measure [Durations(Measure)] which get the selection from user.

Durations(Measure) = IF(HASONEVALUE('Duration'[Duration]),FIRSTNONBLANK('Duration'[Duration Within],1),1)

I want to use this measure for my other table column formula.

However, I cannot get the value correct when I write down the formula in this new column.

my [Durations(Measure)] measure has value 4, however, when I refer it inside my new column formula [DurationfrMeasureIndex], it is 1.

I am doing some debugging and found out the value of measure become 1.

Is there any problem with formatting?

DurationfrMeasureIndex = [Durations(Measure)]*12

 

Thank you

[v-haibl-msft]

@ngadiez

As Vvelarde said, calculated column can’t dynamically change its value. So we have to need to use measure to do the calculation.

I create a test table named YearTable like below. It has no relationship with the Duration table.

We can create a measure with following formula.

Checking =
VAR DM = [Durations(Measure)]
RETURN
    IF (
        COUNTROWS ( FILTER ( YearTable, YearTable[Year] <= DM ) )
            > 0,
        "Yes",
        "No"
    )

Best Regards,
Herbert

[ngadiez]

@v-haibl-msft

Thank you so much for the testing.

I have tried it and it's working.

However, I'm still trying how to incorporate this to mimic the same thing I want to achieve from calculated column. Because I want to do filtering based on the calculated column in DAX.

Anyway, thank you so much for your help.

[v-haibl-msft]

@ngadiez

If it is woking, you can mark the right answer as solution, so that other users who have the same problem can find the solution quickly. 🙂

Best Regards,
Herbert

[ngadiez]

I have found the problem.

Even though, the measure shows the correct number when displayed as a card. 

However, when I pass the measure to calculated column formula, it is assumed to be false.

Why is it so?

Is there any other way so I can pass this to calculated column.

[LaurentCouartou]

My guess:

Your measure works in a card, because in your report, you directly (or indirectly) apply a filter to your Duration table and HASONEVALUE(Duration[Duration]) returns true.

When evaluated in your calculated column, however, HASONEVALUE(Duration[Duration]) returns false, because the Duration table is not filtered, and your measure returns 1.

Can you provide a screenshot of your diagramm view, so we can see how your tables are related?

[LaurentCouartou]

Does the following work?

DurationfrMeasureIndex = CALCULATE( [Durations(Measure)]*12 )

[ngadiez]

No, it didn't work.

[LaurentCouartou]

My guess is, your measure returns 1 because HASONEVALUE('Duration'[Duration]) returns false in your settings.

To help you further, we will need more details about your model.

Edit: Removed some misleading parts in my post.

[ngadiez]

What I want to achieve is to compare the measure with the other column.

[Vvelarde]

@ngadiez

A measure couldn't be use as part of a creation of a Column. 

The columns not dinamically change of value.  the measure working in the context when was called.

[ngadiez]

Is there any other way to do this?

One thing is to use the parameter and filter the data from queries. 

However, I don't want this because it takes too long to load all the data when I change the parameters.