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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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