Creating Measure with Filter against column that contains lists

[jeremyt]

I'm having trouble with a measure and have exhausted my limited knowledge.

I have two tables:

Site_Table, with a field labeled "County" which is a text field that represents the county of the site

I also have a Service_Table that has a field labeled "Taxonomies" that is a list of text values. The current field is a text field (ex: BD-1,BD-2,BD-3), but I also created a column "Taxonomies2" where each cell is a List containing the taxonomy values.

I also have a table labeled "Taxonomies" with all the taxonomies my organization cares about. I'm trying to create a measure in this table so that I can show a card with the number of counties that have a service with a selected taxonomy.

I created the following measure:

Number of Counties Represented = CALCULATE(DISTINCTCOUNT(Site_Table[county]), FILTER(Service_Table, Service_Table[Taxonomies] IN VALUES(Taxonomies[Taxonomies])))

This looked like it worked correctly. I could create the card with the "Number of Counties Represented" measure, and use a slicer of the Taxonomies listed in the Taxonomies table to update the card.

The issue is if a service had multiple taxonomies in the Service_Table[Taxonomies], it wouldn't get counted. So if I set my slicer to taxonomy BD-1, if a service had "BD-1,BD-2" as it's taxonomies Filter doesn't pick that up and I end up having an incorrect county count.

What's the best way to handle this?

[maruthisp · Accepted solution]

Hi @jeremyt ,

Can you try to check attached pbix file to achieve the solution which you are looking for? 

Please let me know if you have any further questions or need clarifications.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[V-yubandi-msft]

Hi @jeremyt ,

Please let us know if the issue is resolved or if you’re still facing any difficulties. Feel free to reach out if you need further assistance.

Thank You.

[V-yubandi-msft]

Hi @jeremyt ,

If you get a chance, please review the response shared by @danextian @maruthisp  and let us know if it aligns with your expectations. Should you need any additional details or clarification, feel free to let us know.

Thank you for your valuable input @maruthisp , @danextian .

Regards,
Yugandhar.

[danextian · Accepted solution]

Hi @jeremyt 

I'm not exactly sure what your tables look like as you did not provide a sample data but assuming that in the service table, one row can have multiple taxonomies separated by a comma, you can virtually expand these and use the expansion in a filter.

Countries Represented = 
VAR ServiceTaxonomyExpanded =
    GENERATE (
        Service_Table,
        VAR _taxString = Service_Table[Taxonomies]
        VAR _taxCount =
            PATHLENGTH ( SUBSTITUTE ( _taxString, ",", "|" ) )
        RETURN
            ADDCOLUMNS (
                GENERATESERIES ( 1, _taxCount ),
                "Taxonomy",
                TRIM (
                    PATHITEM (
                        SUBSTITUTE ( _taxString, ",", "|" ),
                        [Value],
                        TEXT
                    )
                )
            )
    )
VAR FilteredServices =
    FILTER (
        ServiceTaxonomyExpanded,
        [Taxonomy] IN VALUES ( Taxonomies[Taxonomies] )
    )
RETURN
COUNTROWS (
    DISTINCT (
        SELECTCOLUMNS (
            FilteredServices,
            "SiteID", Service_Table[SiteID]
        )
    )
)

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result.

[maruthisp · Accepted solution]

Hi @jeremyt ,

Can you try to check attached pbix file to achieve the solution which you are looking for? 

Please let me know if you have any further questions or need clarifications.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X