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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Uniqueusername
Helper II
Helper II

Adventure Works 2020 Model - blank dates DAX filter context

Hi all,

 

I am trying to understand more about filter contexts in DAX. For this, I have downloaded the Adventure 2020 model from this link https://learn.microsoft.com/en-us/dax/dax-sample-model

I started with creating a a simple matrix visual with "Year" on the rows and a measure "Number of dates in range" which calculates the maximum visible date in the values field

When I do this I am seeing one row in the visual where the year is blank. I am trying to figure out what is causing this. I had a few sales related measures in the visual initally. First thought was there there could be sales in the sales table which do not have a corresponding "order date" in the date table. Removing those sales related measures didn't do anything as I can still see those blank values

Hopefully, the screenshot below might help

Uniqueusername_0-1709423610445.png

 

I also added year slicer. Now this year slicer shows a blank value for some reason. I checked the date table but couldn't find anything

Uniqueusername_0-1709424159593.png

 

Here is the measure I created

 

 

 

Number of dates in range = 
VAR LastdateinContext =
   MAX('Date'[date])
VAR DatesinRange =
    FILTER ( 'Date', 'Date'[Date] <= LastdateinContext )
VAR AllDates =all('Date'[Year])

RETURN
    COUNTROWS(AllDates)

 

 

 


I am sure I am missing something very obvious. But I'm just not able to figure out what that is. I looked at the model to check if there are any odd looking dates in the tables that should not be there but have not been able to find anything

Any help will be much appreciated!

 

Thank you

1 ACCEPTED SOLUTION
talespin
Solution Sage
Solution Sage

hi @Uniqueusername 

 

Watch this Why is there a blank in my Power BI slicer??? - YouTube

 

As soon as you delete the two inactive relationships between date table and othe tables in model, blank row goes away. I am referring to the Adventureworks sample pbix file 2020.

 

 

 

View solution in original post

5 REPLIES 5
Uniqueusername
Helper II
Helper II

I found the culprit. The shipdatekey in the Sales table has blanks. I didn't know inactive relationships on columns with blanks can cause blanks to appear like that

hi @Uniqueusername 

 

Its mentioned in the function details on DAXGuide.

Uniqueusername
Helper II
Helper II

@talespin 

Thanks for that ! Removing the inactive relationships fixed it. But I don't know what is driving that behaviour. I also tried to check for dirty data like in the video you have linked to but couldn't find anything standing out

How would you avoid these blanks in a situation where you certainly require an inactive relationship ?

Thanks

hi @Uniqueusername ,

 

In case its a data issue, you should include blanks.

 

Otherwise you can just apply filter from filter panel to exclude blanks.

talespin
Solution Sage
Solution Sage

hi @Uniqueusername 

 

Watch this Why is there a blank in my Power BI slicer??? - YouTube

 

As soon as you delete the two inactive relationships between date table and othe tables in model, blank row goes away. I am referring to the Adventureworks sample pbix file 2020.

 

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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