Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Evaluate Measure in Calculated Column with Disconnected Table

Hello,

 

I have a disconnected table that has 2 values: 2020 and 2021. It is called "Year Filter" and the column is called "Year Filter".

I have another table that only has a list of periods.

The 2 tables are not related in any way and they cannot be related with a relationship. 

This is part of a bigger logic and for now i am just trying to do some validations.

If i create a measure like below, the logic works fine.

 

 

 

I need to use this measure for further evaluations in a calculated column, let's call it Selection Validation Test. It needs to be a column because i will have to use it as a filter on the page.  

I cannot find a formula that simply displays the same value in the calculated column as in the measure. If i can have this working i can  then add the rest of the logic i need.

 

Thank you for any suggestion

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi Anonymous ,

     

    I create a table: SlicerValue

    Column1
    1
    2
    0

    And a Calculate Table:

    Calculate Table = CROSSJOIN( DISTINCT('Table'[Period]), SlicerValue )

    Then create measure and filter Column1 field by Measure.

    Measure = 
var currentyear = SELECTEDVALUE('Year Filter'[Year Filter])
var selection = IF(currentyear = 2020, 1, IF(currentyear = 2021, 2, 0))
return IF( MAX('Calculate Table'[Column1]) = selection, 1 )

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , You can not use a slicer value or a measure in a calculated column. You have to create another measure for your requirement.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I need it to be a column because i have to create relationships based on it and also use it as a filter on the page. There is no option i can use? thank you

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        5 years ago

        Hi Anonymous ,

         

        What data do you want to filter by measure Selection validation?

         

        The values in calculated column cannot change with the value of slicer. Please share sample data that don't contain sensitive data and expected output, maybe we can do it in a different way.

         

        Best Regards,
        Winniz