Slice Data for column with command seperated values

abhiram342 — Fri, 15 Sep 2023 04:47:51 GMT

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

Re: Slice Data for column with command seperated values

ERD — Fri, 15 Sep 2023 08:04:27 GMT

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 )

topic Re: Slice Data for column with command seperated values in DAX Commands and Tips

Slice Data for column with command seperated values

Re: Slice Data for column with command seperated values