Forum Discussion

Jamesp435's avatar
Jamesp435
Advocate I
3 years ago
Solved

Splitting out from a multi-select dropdown...

Hi team

Forgive me for finding the right words for this but here goes.  The data contains a list of projects.  Against each project there is a column called colours (it actually countries).  Each project might have one or multiple colours associated to it (which are split by a comma). 

I want a simple way of analysing colours to get the following:

The problem is that there are 25 colours.  

I hope this makes sense.

Many thanks 

James 

 



 

 

 

In a table, I want to  

Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Jamesp435 

     

    You can try the following methods.
    In the Power Query:

    Then:

    Count = CALCULATE(COUNT('Table'[Colours]),ALLEXCEPT('Table','Table'[Colours]))

    Measure = CALCULATE(COUNT('Table'[Colours]),ALLEXCEPT('Table','Table'[Colours]))

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

     

3 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, Jamesp435 

     

    You can try the following methods.
    In the Power Query:

    Then:

    Count = CALCULATE(COUNT('Table'[Colours]),ALLEXCEPT('Table','Table'[Colours]))

    Measure = CALCULATE(COUNT('Table'[Colours]),ALLEXCEPT('Table','Table'[Colours]))

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

     

  • Jamesp435's avatar
    Jamesp435
    Advocate I
    3 years ago

    Very sorry but I've haven't thanked for this.  It was very useful.  James