DAX formula

[Sashavg14]

Hi,

I have a slicer that uses this: 

StandardizedName =

SWITCH(

    TRUE(),

    'Connection Table'[Name] = "Tomatoes production", "Tomatoes",

    'Connection Table'[Name] = "Cucumbers production", "Cucumbers",

    'Connection Table'[Name] = "Red bell pepper", "Peppers",

    'Connection Table'[Name] = "Yellow bell pepper ", "Peppers",

    COALESCE('Connection Table'[Name], "Unknown")  // Default case

)
What I now want to achieve, is that when I choose "Tomatoes" from my slicer, I would like to have three sub-options, which should be the material type. For example: "Spanish Tomatoes" , "Portugese Tomates" and "Brazilian tomatoes". Right now, the materials are already connected to the "Tomatoes" - so I am able to choose between the different tomato materials if i add Material.Material Level 01 as a filter to a specific visual. The materials are from another table called "Production Orders" and then from the Column "Material.Material Level 01". But I would like to make it possible to choose the type of material directly under "Tomatoes" from my slicer, which comes from "Connection Table". How do I achieve this?

[v-stephen-msft]

Hi @Sashavg14 ,

Sorry, I dont quite understand your requirement.

Is this your expected result? There're suboptions under 'Tomatoes' in the slicer.

All you need is to put the two fields in the same slicer, it will be the hierarchy slicer. The table visual with data from Table(2) will be filtered beacause of the relationship between the Table and the Table(2).

If you still have doubts, please provide a more detailed description, preferably some virtual sample data, and the expected results.

Best Regards,

Stephen Tao

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

[Sashavg14]

Hi @v-stephen-msft 

Thanks for your help.

What I want to achieve is to add the Material.Material 01 to my slicer, which is from another table. So I want "Mango" to have three suboptions (or whatever the amount of different kind of materials is for that fruit) in the slicer - which all originates from another table. 

[v-stephen-msft · Accepted solution]

Hi @Sashavg14 ,

If you have a clear relationship between Connection Table and Production orders. You can directly put the second field into the slicer. For example, when the two fields are in the slicer, it appears the hierarchy.

If the method like the one above doesn't work, then I guess you should create a table that contains your Test(fruit) column and Material.Material 01 column, and then act as a slicer.

You'll need to create measures as filters to implement filtering for this slicer. Or you can use this new table to create relationships with other tables for filtering.

Best Regards,

Stephen Tao

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

[Sashavg14]

Thanks for your help. I do not have a clear relationship, so I will try to create a "bridge" table. 

[Sashavg14]

To sum up: How do I add 'Productions Orders'[Material.Material Level 01] as options to my slicer from 'Connection Table'[StandardizedName] if the two columns are already related and working as intended. 

[Sashavg14]

The data is sensitive, so unfortunately I cannot share a sample data. 

[danextian]

A sample data doesn't have to sensitive. It's just representation of your actual data. You can rename the values inside the column and possibly randomized the numbers but it must represent the actual data. And from that sample data, the expected result and the reasoning behind.

It is an extra work but we don't wanna be trying to shoot at something without properly aiming at it.

[Tahreem24]

@Sashavg14 Kindly share sample input data example and expected output.