Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
brodyleon
Regular Visitor

Smartsheet Multi-Select > PowerBI Desktop

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:

 

brodyleon_0-1654877067891.png

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. 

 

brodyleon_1-1654877285610.png

 

Brody

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hi, @brodyleon 

Please try formula like:

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

veasonfmsft_0-1655087402388.png

Best Regards,
Community Support Team _ Eason

View solution in original post

1 REPLY 1
v-easonf-msft
Community Support
Community Support

Hi, @brodyleon 

Please try formula like:

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

veasonfmsft_0-1655087402388.png

Best Regards,
Community Support Team _ Eason

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.