Forum Discussion

anacpint's avatar
anacpint
Helper I
3 years ago

Replace blanks after filtering

Hello,

I built this measure:

RFT_v1_Total = VAR Correct =
 
CALCULATE (SUM ( FactRFTJnJPap[Qty_Correct] )
,
DimMeasures[IDMeasureJnJPharma] = 3,
DimTypeReport[IDTypeReportJnJPharma] IN { 1, 5 },
DATESBETWEEN (
DCalendar[FullDate],
EOMONTH ( TODAY (), -12 ),
DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
)
)
VAR Total =
 
CALCULATE (
SUM ( FactRFTJnJPap[Qty_Correct] ) + SUM ( FactRFTJnJPap[Qty_Low] )
+ SUM ( FactRFTJnJPap[Qty_Moderate] )
+ SUM ( FactRFTJnJPap[Qty_Severe] ) ,
DimMeasures[IDMeasureJnJPharma] = 3,
DATESBETWEEN (
DCalendar[FullDate],
EOMONTH ( TODAY (), -12 ),
DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
),
DimTypeReport[IDTypeReportJnJPharma] IN { 1, 5 }
)
RETURN
DIVIDE ( Correct, Total )
 
It works great, but now I need to replace the null values with "N/A" after considering the filters (
DimMeasures[IDMeasureJnJPharma] = 3,
DATESBETWEEN (
DCalendar[FullDate],
EOMONTH ( TODAY (), -12 ),
DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
),
DimTypeReport[IDTypeReportJnJPharma] IN { 1, 5 })
 
I've tried a lot of solutions and all that I get was all the null values with "N/A" but ignoring all the filters.
 
What I need is something like this:

 

Thanks in advance for your help.

Regards,

Ana.

DAX
Filter
replace

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi anacpint ,

     

    Based on your description, you want to replace the null value.
    If you want to replace null values based on the result returned by the measure, you just need to use IF([measure]=BLANK(), "N/A", [measure]), but for your problem, you want to ignore all the filters, I don't know exactly what you mean.
    Can you provide us with some of your sample data and the results you ultimately want to show, so that we can help you better.

     

    Best Regards,

    Neeko Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

     

    • anacpint's avatar
      anacpint
      Helper I
      3 years ago

      Hello Anonymous , thank you for your answer!
      I've tried that solution and it doens't work. No, I need to replace the blanks after execute the measure but considering all the filters. When I try to implement your solution it replaces all the blanks yes, but for all my calendar dates and for all my types of report. I need to replace the blanks, after executing the measure and filtering all the data with the datesbetween condition and just for two types of report.

       

      This is what I have now:

      So, isn't showing any value for both reports in November 21 and for December is just showing data for one of them, because I don't have any record, that's why I need to replace this null values. According with all the requirements I need to display something for the months that don't have any records otherwise I will display an "incomplete" calendar.

       

      Thank you once again.