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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
davidoz
Frequent Visitor

Using date hierarchy and values breaks filters

I posted a question on this yesterday for a different dashboard but saw this extremely frustrating problem reoccur on another dashboard with some more information which might help diagnose the problem.

 

I have this dashboard with a date filter and a column and line chart. Right now it looks like this (confidential information is hidden):

 

image1.jpg

You can see that the visual is correctly picking up the date filter, and limiting the display only to those dates. When I add the following measure:

 

DAYS TO INVOICE TARGET = 10

 

 and drag it into the table, it completely breaks my date filter, and displays this horrible chart instead:

 

image2.jpg

 

Changing the X Axis to Date instead of Date Hierarchy restores to filter, but leaves me with this very ugly and useless chart instead

 

image3.jpg

 

This same problem has now occured with two different dashboards and two different types of visuals. I troubleshooted it with a friend yesterday. He created a measure with a static number, and placed it into a chart like this with no issues. So I don't understand why this is happening in my case and how to resolve it. Any help would be appreciated.

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

Another option is to use a DAX trick to return blank for a measure where another measure has no data.

Constant = DIVIDE ( [Days to Invoice], [Days to Invoice] ) * 10

In my example [Days to Invoice] is whatever measure you are showing on the bars.  If that measure is blank, DIVIDE will return a BLANK and BLANK * 10 returns a BLANK which should stop it from pulling in the extra date values.

If [Days to Invoice] returns an amount DIVIDE [Days to Invoice] by [Days to Invoice] will return 1 then you multiply 1 * 10 to get the 10.

View solution in original post

3 REPLIES 3
jdbuchanan71
Super User
Super User

Another option is to use a DAX trick to return blank for a measure where another measure has no data.

Constant = DIVIDE ( [Days to Invoice], [Days to Invoice] ) * 10

In my example [Days to Invoice] is whatever measure you are showing on the bars.  If that measure is blank, DIVIDE will return a BLANK and BLANK * 10 returns a BLANK which should stop it from pulling in the extra date values.

If [Days to Invoice] returns an amount DIVIDE [Days to Invoice] by [Days to Invoice] will return 1 then you multiply 1 * 10 to get the 10.

Regrettibly, the answer is similar to this. Basically using IF statements to turn the measure into BLANK() when it is not applicable. Although the solution works, it is very frustrating to have to do this. There is clearly something else going on with my PowerBI that I am unable to diagnose properly

jdbuchanan71
Super User
Super User

Not sure why you are seeing the different behaviour from your coworker but you should be able to use the Analysis formatting to add your constant line instead of using a measure.

jdbuchanan71_0-1695994483278.png

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.