topic Why i am getting blank with explicit filter and getting result with implicit filter in calculate fun in DAX Commands and Tips

Why i am getting blank with explicit filter and getting result with implicit filter in calculate fun

Madhu155154 — Mon, 13 May 2024 13:47:40 GMT

Hi Everyone,

When I use implicit, with userelationship function, I can achieve results, but when I use the explicit filter, I get blank result

Note: I have an inactive relationship b/w DOJ and Date. and DOJ contains some blanks

any reason

Implicit filter:

Joining Offerred:=
CALCULATE(
[Count_M],
Main[Joining Status] ="Current Month",
USERELATIONSHIP('Calendar'[Date], Main[DOJ])
)

Explicit filter

Joining Offerred =

CALCULATE(
[Count_M],
FILTER(
Main,
Main[Joining Status] = "Current Month"
),
USERELATIONSHIP('Calendar'[Date], Main[DOJ])
)

Re: Why i am getting blank with explicit filter and getting result with implicit filter in calculate fun

xifeng_L — Mon, 13 May 2024 16:33:03 GMT

Hi Madhu155154 ,

The prerequisite for obtaining results for the Explicit filter measure is that the values of the fields used for active and inactive relationships in the Main table are equal.

Since the filter parameter for the Explicit filter measure uses the entire Main table, the impact of extension-table needs to be considered.

In addition, the internal filter parameters are independent of each other and do not affect each other during calculations.

So the extension-table of the Main table is extended based on the active relationship, and then the USERELATIONSHIP function changes the relationship so that the meaning of the filters for those filters belonging to one end of the extension table changes, but the values of the filters do not change.

Therefore, only when the values of the fields used for active and inactive relationships in the Main table are equal can obtain the non blank result.

The following is a schematic diagram of the calculation process.

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

Thank you~

Re: Why i am getting blank with explicit filter and getting result with implicit filter in calculate fun

Madhu155154 — Wed, 22 May 2024 05:40:40 GMT

But, when I used "keepfilter" instead of "filter" function, I was able to achieve the result

Re: Why i am getting blank with explicit filter and getting result with implicit filter in calculate fun

xifeng_L — Wed, 22 May 2024 09:03:58 GMT

You mean like below?

Joining Offerred:=
CALCULATE(
[Count_M],
KEEPFILTERS(Main[Joining Status] ="Current Month"),
USERELATIONSHIP('Calendar'[Date], Main[DOJ])
)

This is a filter for the status column only, which is equivalent to the

Joining Offerred:=
CALCULATE(
[Count_M],
KEEPFILTERS(FILTER(ALL(Main[Joining Status]),Main[Joining Status] ="Current Month")),
USERELATIONSHIP('Calendar'[Date], Main[DOJ])
)

So it's not a table filter. And if the first parameter of FILTER is a table, it need to consider the impact of extending table, which is what I replied to earlier.

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

Thank you~