Drill down and strange filter behaviour

[Anonymous]

Hello PBI community,

I'm facing a strange behaviour with my PBI report, and I don't really understand why this happens.

I simplified as much as I can the model to be as clear as possible.

First, I have two tables :

I created a measure on Table A. The measure is a DISTINCTCOUNT on "Player".

The table is filtered on the Date field with a Slicer. The aim is to count distinct player on a range date.

The measure is displayed by Country / State / Club. Let call it Measure1, and everything is fine with this one.

Now, I created another measure, Measure2.

This one is  doing the same as Measure1 (distinct count on player), but with an offset on the range date (YEAR-1)

For exemple, if I choose 01/01/2017 to 01/01/2019 as a filter, the result of this Measure2 should be counting players on 01/01/2016 to 01/01/2018 range.

And here is where the trouble's starting.

In my case, I filter on Country. Let say 'UK'.

I'm trying to display the information in a matrix, with Country / State / Club at row level, and my Measure2.

If I display it at Country level it's OK. If I display it at State level, no problem. But when I try to display it at Club level, I got this message :

And I really don't know what can happen when drilling down...

Here is the DAX formula I use for my Measure2 :

Measure2 = var datemin = MIN('Table A'[DATE]) var datemax = MAX('Table A'[DATE])

RETURN CALCULATE((DISTINCTCOUNT('Table A'[Player]));
'Table A'[Type] = "X";'Table A'[DATE] >= DATE(YEAR(datemin)-1 ; MONTH(datemin); DAY(datemin));
'Table A'[DATE] <= DATE(YEAR(datemax)-1 ; MONTH(datemax); DAY(datemax)))

As I said, this formula works lovely... except when I try to display the data at Club level.

I hope I've been clear with my explanations.

Thank you.

Heykel.

[amitchandak · Accepted solution]

@Anonymous , This seems correct, Try with this one change. As I doubt row context

Measure2 = var datemin = MINX(allselected('Table A'),'Table A'[DATE])
var datemax = MAXX(allselected('Table A'),'Table A'[DATE])

RETURN CALCULATE((DISTINCTCOUNT('Table A'[Player]));
'Table A'[Type] = "X";'Table A'[DATE] >= DATE(YEAR(datemin)-1 ; MONTH(datemin); DAY(datemin));
'Table A'[DATE] <= DATE(YEAR(datemax)-1 ; MONTH(datemax); DAY(datemax)))

if it gives an error , just return _min or _max and check what you get. Do you get any blank

[Anonymous]

Thank you Amitchandak, it did work !!

I suspected a contest thing, I tried ALL functions, but ALLSELECTED is all I needed !

Fast and accurate, thank you again 🙂