Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MSemenoff
New Member

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.

1 ACCEPTED SOLUTION
greggyb
Resident Rockstar
Resident Rockstar

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

View solution in original post

7 REPLIES 7
greggyb
Resident Rockstar
Resident Rockstar

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
Not applicable

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

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!

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

@greggyb This is exactly what I needed 😄 thanks

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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