Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Drill down and strange filter behaviour

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 :

HeyTra_1-1600361049522.png

 

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 :

 

HeyTra_2-1600362241287.png

 

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.

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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
Not applicable

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 🙂

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors