Using date hierarchy and values breaks filters

[davidoz]

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):

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:

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

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.

[jdbuchanan71 · Accepted solution]

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.

[davidoz]

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]

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.