Issue with incorrect results of a DAX for sharepoint list multi choice field.

[Michael85]

Good day,

I have a bit of an issue with a DAX that is supposed to count the amount of times a choice appears in a multi choice column of a sharepoint List. 

Basically,

I created a table with the choices of the multi choice column using the following:

FlightDispatchCategories = DATATABLE(
    "Category", STRING,
    {
        {"Baggage"},{"Loading/unloading"},{"Cleaning"},{"Fueling"},
        {"Flight Plan"},{"ATC"},{"Customs"},{"Parking Congestion"},
        {"Missing Authorisation/Permit"},{"Weather"},{"Diversion"},{"Other"}
    }
)

Then I use the following DAX to calcuate each time one appears:

Flight CAT = 
SUMX(
    'Duty Manager Event Log',
    IF(
        CONTAINSSTRING('Duty Manager Event Log'[PurpleType], SELECTEDVALUE('FlightDispatchCategories'[Category])), 
        1, 
        0
    )
)

This somehow returns incorrect values.  Some with too much and some are missing. See below:

In the above chart, there should be additionnal categories and the one displayed as 14 should actually be "10". 

The weird thing is that I did the exact same thing for another field in the same project and it worked fine. 

I would appreciate any insights as I have been struggling with this issue for a week now. 

Thank you

[Michael85 · Accepted solution]

@v-xinruzhu-msft  and @parry2k 

Thank you for your responses.

Unfortunately none seem to work. 

However I was able to find another method using reference Queries and grouping the data.

For now it seems to work. 

Thank you again for your help. 

Edit:   To solve the issue, I went into "transform data". 

Right clicked on my data table, used the reference option.  Using the now created reference query I was able to "expand to new rows" in the Multi choice column. 

Finally, I used the "group by" option in the transform menu.  This created a count of every choice which I was able to use in visuals. 

[v-xinruzhu-msft]

Hi @Michael85 

It is glad that you solved your problem, you can accept you own answer as a solution so that more user can refer to, and if it is convenient for you, you can share your ways here so that it will help more user to solve the similar problem.

Best Regards!

Yolo Zhu

[v-xinruzhu-msft]

Hi,

Thanks for the solution parry2k  offered, and i want to offer some more information for user to refer to.

hello @Michael85 , you can change the measure to the following.

MEASURE =
COUNTROWS (
    FILTER (
        'Duty Manager Event Log',
        CONTAINSSTRING (
            [PurpleType],
            SELECTEDVALUE ( 'FlightDispatchCategories'[Category] )
        )
    )
)

Best Regards!

Yolo Zhu

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

[parry2k]

@Michael85 change SELECTEDVALUE  with MAX and see if it works.