Funky Dax Behavior and Slicers!?

[mzorol]

So I have a visual that displays data from a very large table that is related to a few other tables in a 1 to many.  When I add a simple dax formula to the visual, as "Y", such as:

rate=sum(absent)/count(present)

With the "X" set as a field (staff number) I get the expected results of rate by staff.  These staff members are filtered further by a slicer that filters a related table by site.

If I change the formula to:

rate=1-sum(absent)/count(present)

Then my filter from the related table dissapears and I get staff for the entire table nolonger filtered by site.

How can adding that simple constant cause this issue?  Any ideas?

[xifeng_L · Accepted solution]

Hi @mzorol ,

Because the matrix automatically hides rows that have blank values throughout, after you change the formula to add constants, all rows no longer have blank values, thus making all rows visible, so it looks as if the slicer filtering is disabled.

You can change the formula to the following:

rate=
VAR Val = SUM(absent)/COUNT(present)
RETURN
IF(Val<>BLANK(),VAL)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[mzorol]

Thanks that was the issue.  I modified the formula slightly to att the constant back in so the last line reads:

IF(Val<>BLANK(),1-Val)

Work great.  This was great insight for me to learn.  Thanks again.