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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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