Hiding values (DISTINCTCOUNT) using HASONEFILTER (et al...) is inconsistent: I need enlightenment!!

[PaulDBrown]

I have suddenly (and accidentally...) come across a problem in a matrix  visual. Basically what I'm trying to do is hide the individual "1"s form a DISTINCTCOUNT column to only retain subtotals and totals. Everything is fine, EXCEPT if there are row filters which return a single row: if there is only one row in the filter conext, the row and subtotal display BLANK (which is what the measure is supposed to rerutn but only for the individual rows determined by HASONEFILTER). In other words, if there are 2 or more rows in the filter context, the measure works fine. If there is only one row, the subtotal also returns a BLANK.

Here is a reproduction using a dummy model (Corresponding PBIX File😞

I have tried using HASONEVALUE and ISFILTERED but to no avail. 

This phenomenom occurs if there is a "external" filter applied (slicer or within a Matrix, for example: if the same filters are IMPLICIT to the measure, then it works. So the external filters are what affect the results on a single resulting row in the filter context. 

I can't for the life of me understand why this is so, and what worries me is that I only came across this accidentally. When I created the matrix the numbers looked fine (there were no single rows returned by the filter context, so I did not/ could not spot the problem.

I have found a solution, which is basically to force the measure to return a result if the filter context returns a single row. But it is not elegant and shows a value "1" for the individual row, which is precisely what I'm trying to avoid (otherwise I would just use the simple DISTINCTCOUNT function.

So is this behaviour normal? What's the logic behind it? How can the measure count more than one "blank" but not a single "blank"? (I know this is not what the measure does, but anyway...). Why does HASONEFILTER affect the sub-totals if there is only one row, but doesn't if there are more than one?

Thank you for your assitance in helping me understand this, and if there is a more elegant solution please share it.

Thanks again.

Paul.

[v-yuezhe-msft · Accepted solution]

@PaulDBrown,

The phenomenom about HASONEFILTER() function you have described is normal. Please check the definition HASONEFILTER() function in this official article.

"Returns TRUE when the number of directly filtered values on columnName is one; otherwise returns FALSE."

When you use slicer to filter the Matrix visual to show only a single item, the number of directly filtered values on Item is one, thus the row and subtotal display blank.

There is also a similar blog for your reference.
https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

Regards,
Lydia

[PaulDBrown]

@v-yuezhe-msftThank you very, Lydia, for taking the time to answer, and to provide the link which was very useful. I still however struggle to understand why the behaviour differs when the filters are implicit or explicit to the measure..

Learning DAX is certainly a lengthy, but interesting,  curve! 

Regards,

Paul.