Slice Data for column with command seperated values

[abhiram342]

Hi Team,

I have Fact and Dimension table and One of Column in Fact Table has comma seperated values and I want to slice data when I filter particular value from it. Please find below example

FactPipeline:

DateID	ApplicationName	RunTime (Mins)
9/1/2023	Sales,Insights,Profits	20
9/2/2023	Sales,Insights,Profits	30
9/2/2023	Sales,Insights,Profits	50

DimApplications:

This Table is create by splitting Applications names from FactPipeline.

ApplicationName

Sales

Insights

Profits

If user selects "Sales", then we want to take AverageRunTime. I'm able to create DAX Measure but not sure how to use filter Sales from Facts because it has comma seperated values, if I split "ApplicationNames" into multiple rows then it will result in duplicate values for runtime.

Thanks,

Abhiram

[ERD · Accepted solution]

@abhiram342 , you can use this measure:

AverageRunTime =
VAR appname = SELECTEDVALUE ( Applications[ApplicationName] )
VAR t =
    FILTER (
        ALL ( FactPipeline[ApplicationName] ),
        CONTAINSSTRING ( FactPipeline[ApplicationName], appname )
    )
RETURN
    CALCULATE ( AVERAGE ( FactPipeline[RunTime (Mins)] ), t )