Forum Discussion

chienbap's avatar
chienbap
Frequent Visitor
8 years ago
Solved

Make a Chart with multi choice

Hi All,

 

I have a problem as below:

- I have a list on sharepoint with lookup field and multi choice. In each row, there are many values with comma between them. i can't make a chart with details sum of only value each row.

 

I have a resource list with name Project:

 

In another List: Choice Projects, user can choice multi values like which Data from list above (Project). So, the data in Power BI just get only ID of Project that user chosen):

 And now, i want to make a chart like:

 

In this chart: the Project have to count in all value(the first, the second, or the third)

 

For Example:

there are 3 row that users create:

 

- Row 1: Richstar; Lakeview City

- Row 2: Lakeview City; The Sun Avenue

- Row 3: Botanica; Richstar; Lexington

 

The Chart have to count values like:

Richstar: 2

Lakeview City: 2

The Sun Avenue: 1

Botanica: 1

Lexington: 1

 

Please help me!

Thanks & Best Regards,

  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi chienbap,

     

    You can use below measure to achieve your requirment:

    Total =
VAR _item =
    SELECTEDVALUE ( 'Table'[Index] )
VAR _contains =
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                ALL ( 'Table 2' ),
                "L1", PATHITEM ( SUBSTITUTE ( [Item], ",", "|" ), 1 ),
                "L2", PATHITEM ( SUBSTITUTE ( [Item], ",", "|" ), 2 ),
                "L3", PATHITEM ( SUBSTITUTE ( [Item], ",", "|" ), 3 )
            ),
            _item IN { VALUE ( [L1] ), VALUE ( [L2] ), VALUE ( [L3] ) }
        )
    )
RETURN
    IF ( _item <> BLANK (), _contains )
        + 0

     

    Regards,

    Xiaoxin Sheng

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi chienbap,

       

      You can use below measure to achieve your requirment:

      Total =
VAR _item =
    SELECTEDVALUE ( 'Table'[Index] )
VAR _contains =
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                ALL ( 'Table 2' ),
                "L1", PATHITEM ( SUBSTITUTE ( [Item], ",", "|" ), 1 ),
                "L2", PATHITEM ( SUBSTITUTE ( [Item], ",", "|" ), 2 ),
                "L3", PATHITEM ( SUBSTITUTE ( [Item], ",", "|" ), 3 )
            ),
            _item IN { VALUE ( [L1] ), VALUE ( [L2] ), VALUE ( [L3] ) }
        )
    )
RETURN
    IF ( _item <> BLANK (), _contains )
        + 0

       

      Regards,

      Xiaoxin Sheng

      • chienbap's avatar
        chienbap
        Frequent Visitor
        8 years ago

        Hi Anonymous

         

        Thanks for your help, I have done with your instruction.

         

        Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi all,

     

    Can someone provide some assistance so I can learn to implement the solution described? I am new to Power BI and am looking for some more straightforward instructions to build reports and visualizations that count all unique occurances of values from a column of muli-select options in a sharepoint list.