Forum Discussion

Dayna's avatar
Dayna
Helper V
7 years ago

Nested if / switch on text result

Hello,

 

I'd like a measure that showed a different value for the field depending on the category. I understand I need to aggregate this, but I'm not sure what text aggregation to use.

 

In summary, if type = 1, then I want to show pattern and colour. If type = 2, then I want to show material and category, if type = 3... etc...

 

I got as far as doing this as a switch, rather than nested if, but how do I aggregate this?

 

SWITCH (
TRUE (),
Items[TypeId] = 1 || Items[TypeId] = 2 || Items[TypeId] = 16, VALUES(Applicators[ApplicatorPatterns.Name]),
Items[TypeId] = 3, VALUES(Corrugates[Finish]),
"N/A"
)
 
Many thanks for any assistance!
 
Kind Regards,
Dayna
Need Help

6 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Dayna 

    What do you mean by "aggregate this measure"?

     

    As tested with your formula, it will return only one value for each Items[TypeId].

    Even there are many different values for each id.

    Measure =
SWITCH (
    TRUE (),
    MAX ( Items[TypeId] ) = 1
        || MAX ( Items[TypeId] ) = 2
        || MAX ( Items[TypeId] ) = 16, MAX ( Applicators[ApplicatorPatterns.Name] ),
    MAX ( Items[TypeId] ) = 3, MAX ( Corrugates[Finish] ),
    BLANK ()
)

     

    If you want to return all values for each id, you could create a table

    Table =
UNION (
    UNION (
        FILTER (
            SELECTCOLUMNS (
                Applicators,
                "id", [TypeId],
                "value", [ApplicatorPatterns.Name]
            ),
            [id] = 1
                || [id] = 2
                || [id] = 16
        ),
        FILTER (
            SELECTCOLUMNS ( Corrugates, "id", [TypeId], "value", [Finish] ),
            [id] = 3
        )
    ),
    FILTER (
        SELECTCOLUMNS ( Items, "id", [TypeId], "value", " " ),
        [id] <> 1
            && [id] <> 2
            && [id] <> 3
            && [id] <> 16
    )
)

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Dayna's avatar
      Dayna
      Helper V
      7 years ago

      Hi Maggie,

       

      Many thanks for your help, the aggregration I think I meant was something like the MAX that you put into your formula, as that brings back the results as I'd expect.

       

      As far as the table, the TypeId comes from a table called 'items', whereas the value (i.e. the Applicator Pattern) comes from the applicator table. There is a relationship between these two tables. However, the example you provided doesn't work as it is looking for the TypeId within the Applicators table, which doesn't exist. Can you advise, please?

       

      Many thanks for your help, it is much appreciated!

       

      Kind regards,

      Dayna 

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi Dayna 

        "However, the example you provided doesn't work as it is looking for the TypeId within the Applicators table, which doesn't exist."

         

        Sorry, i'm not clear.

        Could you clear me?

         

        You can give an example for solution 2- create a table:

        with my test tables, what's expected table?

         

        Best Regards
        Maggie

         

        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Dayna 

    Is there any relationship between two tables below?

    Items table

    TypeId
    1
    2
    3

    Applicators table

    ApplicatorPatterns.Name
    pattern
    colour
    material
    category

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Dayna's avatar
      Dayna
      Helper V
      7 years ago

      Hi Maggie, 

       

      There is indeed, it's a one to many relationship from the items table, to the applicators table.

       

      It is a direct relationship; but the applicator table has had a few other tables merged into it. Not sure if that makes a difference!

       

      Many thanks for your help,

      Dayna