Forum Discussion

ngadiez's avatar
ngadiez
Icon for Helper II rankHelper II
9 years ago

Measure value is not right

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

Need Help

11 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      ngadiez
      Icon for Helper II rankHelper II
      9 years ago

      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's avatar
        v-haibl-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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

         

  • LaurentCouartou's avatar
    LaurentCouartou
    Icon for Solution Supplier rankSolution Supplier
    9 years ago

    Does the following work?

     

    DurationfrMeasureIndex = CALCULATE( [Durations(Measure)]*12 )
      • LaurentCouartou's avatar
        LaurentCouartou
        Icon for Solution Supplier rankSolution Supplier
        9 years ago

        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's avatar
    ngadiez
    Icon for Helper II rankHelper II
    9 years ago

    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's avatar
      LaurentCouartou
      Icon for Solution Supplier rankSolution Supplier
      9 years ago

      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?