CONTAINSTRING help on row by row values

[thomazinh]

I'm trying to return a calculation that has is filtered by a numerator and denominator based on the ID selected out of the range of values. For each ID, I want to take the division of a selected few disciplines (e.g. VCD, QC, PM, PCN, PE) over another few (SMI, PFI, and PLI). Not every Id will have all attributes of the numerator but all Id's will have the same attributes of the denominator (SMI, PFI, and PLI).  

I had a hard time figuring out how to get a fixed denominator for a dynamic numerator, but now my numerator is also fixed based on the selections above. I need to to return those values per ID row in a table. So for the below snippet example, I'd expect the following result. 

DAX Sample is below. 

VAR _NumFilter =
    CALCULATE(
        SUMX(
            'WbsCode Table',
            CONTAINSSTRING('WbsCode Table'[WbsCode.Discipline.name], {"VCD", "QC", "PM", "PCN", "PE" })), 'WbsCode Table'[ActualHrs])

VAR _numerator =
    CALCULATE(SUM ( 'WbsCode Table'[ActualHrs] ), _NumFilter )
VAR _Filter =
    FILTER (
        ALLSELECTED ( 'WbsCode Table' ),
        'WbsCode Table'[WbsCode.Discipline.name] IN { "PLI", "PFI", "SMI" }
    )
VAR _denominator =
    CALCULATE ( SUMX 'WbsCode Table'[ActualHrs]), _Filter ))
VAR _Result =
    DIVIDE ( _numerator, _denominator, BLANK() )
RETURN
    _numerator 

[v-yalanwu-msft · Accepted solution]

Hi, @thomazinh ;

Try it.

Numerator = 
var _a=SUMMARIZE(FILTER(ALL('WbsCode Table') ,[WbsCode] in ALLSELECTED('Table'[WbsCode] )),[WbsCode])
return CALCULATE(SUM([Hours]),FILTER(ALL('WbsCode Table'),[WbsCode] in _a&&[Id]=MAX([Id])))

Measure = 
var _a=SUMMARIZE(FILTER(ALL('WbsCode Table'),NOT([WbsCode]) in ALLSELECTED('Table'[WbsCode]) &&
          CALCULATE(DISTINCTCOUNT('WbsCode Table'[Id]),FILTER(ALL('WbsCode Table'),[WbsCode]=MAX('WbsCode Table'[WbsCode])))=3),[WbsCode])
return CALCULATE(SUM([Hours]),FILTER('WbsCode Table',[WbsCode] in _a))

Numerator = 
var _a=SUMMARIZE(FILTER(ALL('WbsCode Table') ,[WbsCode] in ALLSELECTED('Table'[WbsCode] )),[WbsCode])
return CALCULATE(SUM([Hours]),FILTER(ALL('WbsCode Table'),[WbsCode] in _a&&[Id]=MAX([Id])))

Result = [Numerator]/[_denominator]

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[thomazinh]

@v-yalanwu-msft is there a way to do this same measure without the slicer selection?