Make a Chart with multi choice

[chienbap]

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]

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. 

[chienbap]

pls help me,

many thanks

[v-shex-msft · Accepted solution]

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]

Hi @v-shex-msft

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

Regards,