Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Exclude 0 and blank from my Output

Hi Team,

 

Need qucik help,

I have a measure and my ouput but I have to exclude 0 and blanks from output, I can do it in Visual level filter but instead need to get it from my measure directly, please help me.

Monday_Snapshot_Total =
var selectedMondayDate = MIN(KH_Calendar[Date])+1
VAR WeekDates   = DATEADD(KH_Calendar[Date],MAX(Frozen_Horizon[Fixed Horizon (Weeks)])*7,DAY)
VAR total = CALCULATE(SUM(EU_OMP_PROD_LOC_PLANVIEW_DAILY_FACT[QUANTITY]),
WeekDates,
EU_OMP_PROD_LOC_PLANVIEW_DAILY_FACT[SNAPSHOT_DATE].[Date] = selectedMondayDate)
RETURN total
 
 
Thanks!
Need Help

2 Replies

  • hackcrr's avatar
    hackcrr
    Memorable Member
    2 years ago

    Hi, Anonymous 

    To exclude zero and blank values directly in your measure, you can modify your DAX code to return BLANK() when the result is zero or null. This way, you won't need to rely on visual-level filters. Here's an updated version of your measure:

    Monday_Snapshot_Total =
VAR selectedMondayDate = MIN(KH_Calendar[Date]) + 1
VAR WeekDates = DATEADD(KH_Calendar[Date], MAX(Frozen_Horizon[Fixed Horizon (Weeks)]) * 7, DAY)
VAR total = CALCULATE(
    SUM(EU_OMP_PROD_LOC_PLANVIEW_DAILY_FACT[QUANTITY]),
    WeekDates,
    EU_OMP_PROD_LOC_PLANVIEW_DAILY_FACT[SNAPSHOT_DATE].[Date] = selectedMondayDate
)
RETURN IF(total = 0 || ISBLANK(total), BLANK(), total)

     

    Best Regards,

    hackcrr

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      hackcrr ,

       

      Thanks for your response ,
      After modifying I still see blanks

      Solver Quantity =
      VAR selectedMondayDate = MIN(KH_Calendar[Date]) + 1
      VAR WeekDates = DATEADD(KH_Calendar[Date], MAX(Frozen_Horizon[Fixed Horizon (Weeks)]) * 7, DAY)
      VAR total = CALCULATE(
          SUM(EU_OMP_PROD_LOC_PLANVIEW_DAILY_FACT[QUANTITY]),
          WeekDates,
          EU_OMP_PROD_LOC_PLANVIEW_DAILY_FACT[SNAPSHOT_DATE].[Date] = selectedMondayDate
      )
      RETURN IF(total = 0 || ISBLANK(total), BLANK(), total)