Nested if / switch on text result

[Dayna]

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

[v-juanli-msft]

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]

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]

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.

[Dayna]

Hi Maggie,

Of course. I have three tables: "Items", "Corrugates" and "Applicators".

Corrugates links to the Items table on Id (Corrugates) to Id (Items)

Applicator links to the Items table on Id (Applicator) to Id (Items)

My end result is to be able to have one table like your original post, where there's the Id and the Value. 

Example items data:

Id	Item Types.Items.TypeName	TypeId
31721	Corrugated	1
31722	Corrugated	1
31724	Corrugated	1
31725	Corrugated	1
31726	Corrugated	1
31727	Corrugated	1
31728	Corrugated	1
31729	Corrugated	1
31731	Corrugated	1
31732	Corrugated	1
31733	Corrugated	1
31734	Corrugated	1
31736	Corrugated	1
31737	Corrugated	1
31739	Corrugated	1
31740	Corrugated	1
31741	Corrugated	1
31742	Corrugated	1
31743	Corrugated	1
31744	Corrugated	1
38083	Applicator	1
38084	Applicator	1
38085	Applicator	1
38086	Applicator	1
38087	Applicator	1
38088	Applicator	1
38089	Applicator	1
38090	Applicator	1
38091	Applicator	1
38092	Applicator	1
38093	Applicator	1
38094	Applicator	1
38095	Applicator	1
38096	Applicator	1
38097	Applicator	1
38098	Applicator	1
38099	Applicator	1

Example Applicator Data:

IdApplicatorTypes.NameApplicatorPatterns.Name

37836	Airlaid
37837	Thermobonded	Dots
37838	Airlaid	Plain
37839	Dispersible Wetlaid	Plain
37840	Spunlace	Plain
37841	Airlaid	Toys
37842	Airlaid	Toys
37843	Spunlace	Plain
37844	Airlaid
37845	Airlaid	Toys
37846	Spunlace	Plain
37847	Spunlace	Plain
37848	Dispersible Wetlaid	Plain
37849	Dispersible Wetlaid	Plain
37850	Spunlace	Plain
37851	Airlaid
37852	Airlace	Plain
37853	Airlaid

Example Corrugate Data:

IdFinish

31721	Plain
31722	Plain
31724	Plain
31725	Plain
31726	Printed
31727	Plain
31728	Plain
31729	Plain
31731	Plain
31732	Plain
31733	Plain
31734	Plain
31736	Plain
31737

Hopefully that clarifies it?

Many thanks for your help!

Kind Regards,

Dayna

[v-juanli-msft]

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]

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