DAX Studio - Filter with NOT(ISNULL([METRIC]))

Question

I'm thinking my problem is coming from not understanding the order of operations DAX uses because its super confusing to me, but here is my problem:

I have a fairly simple query using SUMMARIZE that basically pulls in a field I want and then calculates several metrics - each of which are filtered within the CALCULATE function. But I want to exclude all rows that end up with a null value due to no data being available at that level. I'm not on my computer with the code or I would put exactly what I have here, but a simplified version is basically like this:

EVALUATE
FILTER(
    SUMMARIZE(
         'Fact Table',
         FieldTable[Field1],
         'Metric1',
         CALCULATE(
              [MetricA]
              FILTER(
                   'Fact Table'
                   [MetricA],  
                   [Year] = 2017
              )
          ),
          'Metric2',
          CALCULATE(
              [MetricB]
              FILTER(
                   'Fact Table'
                   [MetricA],  
                   [Year] = 2016
              )
          )
     ),
     NOT(ISNULL([Metric1]))
)

Hopefully I got all that right. I'm don't have DAX studio in front of me to fix my problems so there might be minor errors, but hopefully you get the gist of it.

The problem is that this returns a blank table. If I take out the FILTER surrounding the SUMMARIZE function then everything works exactly like I want it to, except it brings in a ton of blank rows, which is what I'm trying to eliminate with the FILTER on the outside. Any thoughts on how to do that?

JMorr JMorr · Accepted Answer · 2017-10-02T17:28:11

I don't know if this is the best solution, but I figured out a solution. Basically, add 0 to my measures and then change the filter to filter on <> 0.

EVALUATE
FILTER(
    SUMMARIZE(
         'Fact Table',
         FieldTable[Field1],
         'Metric1',
         CALCULATE(
              [MetricA]
              FILTER(
                   'Fact Table'
                   [MetricA],  
                   [Year] = 2017
              )
          ) + 0,
          'Metric2',
          CALCULATE(
              [MetricB]
              FILTER(
                   'Fact Table'
                   [MetricA],  
                   [Year] = 2016
              )
          )
     ),
     [Metric1]<>0
)

DAX Studio - Filter with NOT(ISNULL([METRIC]))

1 Answers