Forum Discussion

RoxanaZaharia's avatar
RoxanaZaharia
Helper I
2 years ago
Solved

need help with this table visualization

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 de...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
RoxanaZaharia
Helper I
2 years ago

Hi Anonymous ,

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?

 



  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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!