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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
JMac_WandB
Frequent Visitor

Filter blanks from calculated table based on measure

Hi all,

 

Hopefully a simple solution: I have a calculated table which brings in a measure (NTA_Ch) which is working fine, my issue is adding a FILTER to exclude values where the measure does not apply. It currently includes a first row (-100%) that shouldn't be calculated and also trailing values (pictured).

 

I had expected to be able to include "FILTER (Table, underlying number <> BLANK())" however this seems to change the context of the measure and break the calculation? Any thoughts on a way around this? 

 

NTA_MoM2 =
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZECOLUMNS('Date_Ref$'[Cal_Period],Summary[Code]),
"NTA_MoM", [NTA_Ch])
 
)
trailing values are zerotrailing values are zerofirst value shouldn't existfirst value shouldn't exist
1 ACCEPTED SOLUTION

Sorry, but I cannot fully understand the related tables and measures.

Please try the below whether it suits your request. I think you can add more conditions to the below if you need more to filter.

 

NTA_MoM2 =
FILTER(
SUMMARIZECOLUMNS('Date_Ref$'[Cal_Period],Summary[Code],
"NTA_MoM", [NTA_Ch]), [NTA_MoM]<>-1)
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

6 REPLIES 6
Jihwan_Kim
Super User
Super User

How does the measure [NTA_Ch] look like?

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Hi Jihwan,

 

It is a calculation based on a series of other calculations:

NTA_Ch = iferror(([NTA_Total_Return]/[TR_LastMonth2])-1, BLANK())

Please share the sample pbix file's link.


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Sorry, but I cannot fully understand the related tables and measures.

Please try the below whether it suits your request. I think you can add more conditions to the below if you need more to filter.

 

NTA_MoM2 =
FILTER(
SUMMARIZECOLUMNS('Date_Ref$'[Cal_Period],Summary[Code],
"NTA_MoM", [NTA_Ch]), [NTA_MoM]<>-1)
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Many thanks Jihwan, Looks like this will do the trick.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors