Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate function using Containsstring filter not working as expected

Hey Guys,   I'm sure I'm missing something small in the syntax here but I've tried this DAX measure about every way possible the past couple days without any luck.    I have a table called 'vw_sh...
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    Hi Anonymous ,

     

    Maybe you could try this Measure.

    ACT% =
DIVIDE (
    CALCULATE (
        SUM ( vw_shrinkage_act[Total_Hrs] ),
        FILTER ( vw_shrinkage_act, vw_shrinkage_act[Shrinkage_cat_code] <> "SHIFT" )
    ),
    CALCULATE (
        SUM ( vw_shrinkage_act[Total_Hrs] ),
        CONTAINSSTRING ( vw_shrinkage_act[Shrinkage_cat_code], "SHIFT" )
    )
)

     

    Then the result will look like this:

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hey thanks for writing up these examples. We are super close here. The only issue left is that "Tot Shift" being MAX(value) isn't usable. My source table has many thousands of rows, there are many values for "Shift" being Summed and aggregated, so dividing by the maximum value of Shift yields incorrect math. I tried making some changes but I guess I can't use SELECTEDVALUE on a calculated measure. 

Whitewater100's avatar
Whitewater100
Solution Sage
4 years ago

Hi:

No problem. They are based on what you shared. You can have a variable in a calculated measure.

Measure Name

var shift = Table[Shift[ID]

return

the DAX referring to this variable.  Example only.

return

Do you have example data(Model-table-visual you want to create) that reflects all the nuances  to writing the DAX?