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

[Madhu155154]

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

[Madhu155154]

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

[xifeng_L · Accepted solution]

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~

[xifeng_L]

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~