topic Re: Calculate function using Containsstring filter not working as expected in DAX Commands and Tips

Calculate function using Containsstring filter not working as expected

Anonymous — Mon, 28 Mar 2022 20:42:33 GMT

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_shrinkage_act' and I'm using two columns to derive a calculated measure, [Total_Hrs] and [Shrinkage_cat_code] . I break these down by day of week in the matrix tables below.

I'm just trying to divide the Total Hrs column by itself with help of filters. The denominator needs to be category "Shift" and the numerator needs to be all the other category codes EXCEPT for "Shift" as it just adds itself (100%) to the overall sum which isn't helpful.

What seems to work, but without filtering out "Shift" from numerator: Visual breaks if I deselect "Shift" in a slicer

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

What I expected to work, but it does some weird aggregation that's completely not helpful...

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

Re: Calculate function using Containsstring filter not working as expected

Whitewater100 — Mon, 28 Mar 2022 21:12:39 GMT

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]))

Re: Calculate function using Containsstring filter not working as expected

Anonymous — Tue, 29 Mar 2022 16:40:56 GMT

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

Category	Value
Absence	12
Leave	4
Break	3
Shift	82

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.

Re: Calculate function using Containsstring filter not working as expected

Whitewater100 — Tue, 29 Mar 2022 17:43:02 GMT

Hi:

I understand. Can you try this link for report? There is a table with calculation as well, but most are measures. I hope this helps!

https://drive.google.com/file/d/1O81vtC1QOyE_hoiLrBttXw-6Ibr9X1uE/view?usp=sharing

Re: Calculate function using Containsstring filter not working as expected

Anonymous — Tue, 29 Mar 2022 19:10:24 GMT

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.

Re: Calculate function using Containsstring filter not working as expected

Whitewater100 — Tue, 29 Mar 2022 20:36:42 GMT

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?

Re: Calculate function using Containsstring filter not working as expected

v-cazheng-msft — Fri, 01 Apr 2022 14:17:03 GMT

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