Forum Discussion

vzbkb1's avatar
vzbkb1
Helper II
3 years ago
Solved

Calculated Column based on different values

I hope that you can help me.  Let me try to explain you the data that I have and the result expected:   Having the following list     ID Name Status 1 Jane Validated 2 ...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi vzbkb1 ,

    I understand, you can create another column:

    Column 2 =
IF (
    'Table'[ID]
        = MINX (
            FILTER (
                'Table',
                'Table'[Name] = EARLIER ( 'Table'[Name] )
                    && 'Table'[Status] = 'Table'[Column]
            ),
            'Table'[ID]
        ),
    1,
    0
)

    Result:

    Now it works:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

  • vzbkb1's avatar
    vzbkb1
    3 years ago

    Great!!!  Yes, that it was I was looking for.  Thanks a lot for your help and your quick responses

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi vzbkb1 ,

    I understand😊.

    Modify the formula:

    Column =
VAR _T =
    FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) )
RETURN
    IF (
        COUNTROWS ( FILTER ( _T, [Status] = "Validated" ) ) > 0,
        "Validated",
        IF (
            COUNTROWS ( FILTER ( _T, [Status] IN { "Error", "Affected" } ) ) > 0,
            MAXX (
                FILTER (
                    _T,
                    [ID]
                        = MINX ( FILTER ( _T, [Status] IN { "Error", "Affected" } ), [ID] )
                ),
                'Table'[Status]
            ),
            IF ( COUNTROWS ( FILTER ( _T, [Status] = "Warming" ) ) > 0, "Warming" )
        )
    )

    I modify the sample and get correct result.

    Best Regards,
    Community Support Team _ kalyj

vzbkb1's avatar
vzbkb1
Helper II
3 years ago

It works perfectly thank you 🙂  I just had an small issue now.  I will need to use the filster not only to this table but also to the page where I have some counters, and the issue is that I cannot use the measure created to put it as a filter page.  ¿Is possible to have a new column with the same values as those in the measure created?  I have tried, but I always get 0 😞

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi vzbkb1 ,

    Sorry maybe I'm not very clear about your desired result. Isn't this:

    If not, could you please show me the result.

     

    Best Regards,
    Community Support Team _ kalyj

    • vzbkb1's avatar
      vzbkb1
      Helper II
      3 years ago

      The result is perfect.  My question came mainly because I want to use the mease created to filter the page but that it is not possible.  For example, if I try to include a card with the total of names, I get 14 but really I only want to get 5.  That is the reason that I was thinking that if instead the measure I could have another column to contain de values 0 and 1 (same as the mesure that you proposed)

       

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Community Support
        3 years ago

        Hi vzbkb1 ,

        I understand, you can create another column:

        Column 2 =
IF (
    'Table'[ID]
        = MINX (
            FILTER (
                'Table',
                'Table'[Name] = EARLIER ( 'Table'[Name] )
                    && 'Table'[Status] = 'Table'[Column]
            ),
            'Table'[ID]
        ),
    1,
    0
)

        Result:

        Now it works:

        I attach my sample below for your reference.

         

        Best Regards,
        Community Support Team _ kalyj

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