need help with this table visualization

[RoxanaZaharia]

Hello! I have this dataset (as seen in the photo), and I want to create a table visualization listing only the types of cheeses that the restaurant uses. I tried splitting the column using a comma delimiter, but when I attempted to Close and Apply, an error occurred. It says that the column 'pizza_type_id' in Table 'pizza_types' contains a duplicate value 'bbq_ckn,' which is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table. So I tried making a measure to show the list of cheeses, but I can't seem to figure out what combination of functions to use to show me the result I'm looking for. Can someone explain to me how I can approach this problem?

[v-huijiey-msft]

Hi @RoxanaZaharia ,

Ok, So:

First, delete the relationship between the pizzas table and the pizza_types table.

Enter Power Query and separate by commas.

After Close and Apply, establish the relationship between the pizzas table and the pizza_types table. The relationship at this time is *:*.

A many-to-many relationship is used between two tables when neither table contains unique values.

The pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[RoxanaZaharia]

Hi @v-huijiey-msft ,

What you showed me worked. I don't get the error anymore, but I still have a problem with the list of types of cheese I am trying to show. I don't get why 'Mozzarella Cheese' is the only one that appears twice. How can I make sure only one appears? Also, I want to create a measure that counts the number of types of cheese, but how can I ensure that this measure doesn't count Mozzarella Cheese twice?

[v-huijiey-msft · Accepted solution]

Hi @RoxanaZaharia ,

This problem may occur because there are spaces before the words.

Just remove the spaces in Power Query.

Create a measure to determine whether it is cheese:

isCheese =
VAR a = CONTAINSSTRING(MAX('pizza_types'[ingredients]),"Cheese")
RETURN IF(a,1,0)

Create a table:

Table = CALCULATETABLE(
     SUMMARIZE('pizza_types',
     'pizza_types'[ingredients]),
     FILTER('pizza_types','pizza_types'[isCheese] = 1)
     )

Create a count measure:

Count = DISTINCTCOUNT('Table'[Types of Chees])

The final page effect is as follows:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[v-huijiey-msft]

Hi @RoxanaZaharia ,

Thanks for the reply from @HotChilli .

With the screenshot I can not help you with the problem very well.

Error: Column “pizza_type_id” in table “pizza_types” contains duplicate value “bbq_ckn”.

Can you try using comma separators after removing the duplicates in Power Query?

I would appreciate if you could provide me with the pbix file and remember not to log into your account in Power BI Desktop when uploading the pbix file.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[RoxanaZaharia]

Hello @v-huijiey-msft, 

I haven't figured out how to resolve the problem. This is the pbix file I'm working with. https://drive.google.com/file/d/1iyU-6URGQZSttlZJOKq4OwFGbqZckHKi/view?usp=sharing

[HotChilli]

It looks like there are existing relationships in the model.  When you split the column (to rows?) it produced multiple rows and then broke the existing relationships.  You might want to delete the relationships (all of them) and try splitting again.

Maybe this gets you further but it's a slightly different issue than 'show me all the cheeses'.  Do you want a separate table with cheeses or are these only important with the different pizzas?  And that is a sentence i did not think i would write today!