Forum Discussion
Eliminating a blank from a table visualization
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.
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] ) ) )
7 Replies
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] ) ) )- Thank you. I'll try both suggestions. Lots to learn.
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
greggyb This is exactly what I needed :D thanks
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
