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

Whitewater100's avatar
Whitewater100
Solution Sage
4 years ago

Hi:

Instead of using ContainsString can you consider trying a measure like:

Share = 

= DIVIDE(SUMX(

vw_shrinkage_act[Total_Hrs]

),

SUMX(ALLSELECTED(

vw_shrinkage_act), [Total Hours]))

Anonymous's avatar
Anonymous
Not applicable
4 years ago

That doesn't quite work for what I'm trying to do. Example table below

CategoryValue
Absence12
Leave4
Break3
Shift82


Absence / Shift = 0.146 or 14.6%
Leave / Shift = 0.048 or 4.8%
Break / Shift = 0.036 or 3.6%
All / Shift = 0.231 or 23.1%

 

The problem I run into is that Shift / Shift = 100% and it gets added to the total values which just makes everything wrong. "Shift" is only there to be the denominator in the division, I need it excluded from the numerator which is where I got to the two measures I originally posted. 

    • 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?