Eliminating a blank from a table visualization

[MSemenoff]

I'm new to DAX. I'm creating a measure using two two tables. The "one to many" relationship between the tables stems from columns of unique IDs. However the "one" side column is a subset of the "many" side column. When I make a table visualization, I'm getting a blank in the ID column and a total for all the IDs from the "many" side column not included in the "one" side column.

It looks like this:

ID          Amt. 1      Amt.2

blank     $$$$$$            

1           $$$$$$    $$$$$$

2           $$$$$$    $$$$$$

and so on.

The Amt.1 measures are coming from the "many" side, the Amt.2 measures from the "one" side.

How can I eliminate that "blank"? What DAX code should I use? Thanks for any sufggestions.

[greggyb · Accepted solution]

You can filter the table using visual-, page-, or report-level filters to exclude blanks from the one side.

Or you can rewrite your [Amt1] measure to do the same:

Amt1 =
CALCULATE(
    SUM( '<many side table>'[Amount] )
    ,NOT( ISBLANK( '<one side table>'[ID] ) )
)

[Anonymous]

What if you want to include the blank in the total calculation but not as a row in the visualisation?

[euthor]

The filtering tip actually worked well also for my problem, however I still don't get why I see the "blank" when I switch to the data visualisation of my PBIX file. Does anyone know how can I eliminate the blan values from each column of the data visualisation file from which the report takes all of the data? Thank you PBIX community, you are awesome!

[akruzel]

Hello,

       So what if the Amt1 is blank?  Is there a way to do this in DAX?  There's a suggestion in another thread to use the Visual Filter, but the Visual Filter appears to break if for example you have a bar graph and there is a hierarchy to drill down.  The lower level will work with filtering out blanks but appears to break if you drill up.

Adam

[mbdtz]

@greggyb This is exactly what I needed 😄 thanks

[MSemenoff]

Thank you. I'll try both suggestions. Lots to learn.

[MSemenoff]

The report level filter did the trick. The measure gave the grand total. Not sure why, maybe becuse the columns had no blanks.

Anyway, you suggestion worked. Thanks agian