Smartsheet Multi-Select > PowerBI Desktop

[brodyleon]

Hi - 

I am looking for some help calculating the number of times each of my departments is tagged in an audit findings.  We are managing our audits in Smartsheet which has a multi-select dropdown feature - this allows me to tag multiple departments to a single audit finding.  Smartsheet exports the multi-select dropdown as a list as shown below:

We currently have 30+ departments and I am trying to write a DAX formula to count the number of times each department is tagged.  I am looking for a dynamic solution which will not require me to create 30+ measures......does anyone have any ideas on how to create a formula for the whole column to calculate......

Currently I have a separate table with a list of the departments - I am trying to calculate the number of time each department appears in the table posted above with the audit findings.  These are two separate tables. 

Brody

[v-easonf-msft · Accepted solution]

Hi, @brodyleon 

Please try formula like:

RESULT =
CALCULATE (
    COUNTROWS ( Table1 ),
    CONTAINSSTRING ( Table1[Department], MAX ( Table2[Department Name] ) )
)

Best Regards,
Community Support Team _ Eason