Forum Discussion

JMac_WandB's avatar
JMac_WandB
Frequent Visitor
5 years ago
Solved

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 zerofirst value shouldn't exist
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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)
     
     

6 Replies

    • JMac_WandB's avatar
      JMac_WandB
      Frequent Visitor
      5 years ago

      Hi Jihwan,

       

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

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