Forum Discussion

abhiram342's avatar
abhiram342
Microsoft Employee
2 years ago
Solved

Slice Data for column with command seperated values

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:

DateIDApplicationNameRunTime (Mins)
9/1/2023Sales,Insights,Profits20
9/2/2023Sales,Insights,Profits30
9/2/2023Sales,Insights,Profits50

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

"FILTER"
"Need Help"
Data Modelling
  • ERD's avatar
    ERD
    2 years ago

    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 )

     

1 Reply

  • ERD's avatar
    ERD
    Community Champion
    2 years ago

    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 )