Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filters does not work when +0 is added to remove blanks

Hello, 

I need to visualize the last 12 months' data from a selected date, for that, I have created a date table(DTEntryDateTable) that is not connected with the fact table and used this measure to visualize 12-month data in the bar chart. 

 

 

Measure=
VAR CurrentDate = MAX(DTEntryDateTable[dteCreatedOn])
VAR PreviosData = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))
VAR Result = 

CALCULATE(
    COUNT(tblIndexDT[numIndexID]),
    
    FILTER(
        tblIndexDT,
        tblIndexDT[dteCreatedOn]>=PreviosData && tblIndexDT[dteCreatedOn]<=CurrentDate
    )
)
RETURN
Result

 

 

This measure works fine and it does the job, But I need to show blank values as "0", Therefore I changed the measure into this,

 

 

Measure=
VAR CurrentDate = MAX(DTEntryDateTable[dteCreatedOn])
VAR PreviosData = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))
VAR Result = 

CALCULATE(
    COUNT(tblIndexDT[numIndexID])+0,
    
    FILTER(
        tblIndexDT,
        tblIndexDT[dteCreatedOn]>=PreviosData && tblIndexDT[dteCreatedOn]<=CurrentDate
    )
)
RETURN
Result

 

 

But when I add +0 for the count, it ignores the filter in Dax and shows data for every month which is available in the dataset with zeros for blank values.

Please help me to figure out a way to show zeros for blank values without losing the filters in DAX.

Thank you.

Need Help
Tutorial Requests
  • Pragati11's avatar
    Pragati11
    5 years ago

    Hi Anonymous ,

     

    Can you try the following DAX:

     

    Measure=
VAR CurrentDate = MAX(DTEntryDateTable[dteCreatedOn])
VAR PreviosData = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))
VAR Result = 
CALCULATE(
    COUNT(tblIndexDT[numIndexID]),
    FILTER(
        tblIndexDT,
        tblIndexDT[dteCreatedOn]>=PreviosData && tblIndexDT[dteCreatedOn]<=CurrentDate
    )
)
RETURN
IF(ISBLANK(Result), 0, Result)

     

    Thanks,

    Pragati

  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Anonymous 

     

    The data that was hidden by the blank value is now displayed as 0. You can add a date slicer to filter data from March 2023 to March 2021 or use filter pane to filter date column. 

     

    Best Regards

    Janey Guo

     

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

10 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous ,

     

    Try chnaging your measure to as follows:

    Measure=
VAR CurrentDate = MAX(DTEntryDateTable[dteCreatedOn])
VAR PreviosData = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))
VAR Result = 

CALCULATE(
    COUNT(tblIndexDT[numIndexID]),
    
    FILTER(
        tblIndexDT,
        tblIndexDT[dteCreatedOn]>=PreviosData && tblIndexDT[dteCreatedOn]<=CurrentDate
    )
)
RETURN
Result + 0

     

    Thanks,

    Pragati 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you Pragati11  for your response,

      But still, it does not work.

      ☹️

      • Pragati11's avatar
        Pragati11
        Icon for Super User rankSuper User
        5 years ago

        Hi Anonymous ,

         

        I guess then you will have to add more details on your data and calculations.

         

        Thaks,

        Pragati

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    According to your description, Your formula is correct, but there is a problem with the result, So can you share some sample fake data and your visual result like? We need to judge the context.

     

    Best Regards

    Janey Guo

     

    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
      5 years ago

      Hello Janey Guo,

      Thank you for the response.😊

       

      This is the output from the current measure,

      Here I want to display zero for blanks

      Details of above-mentioned matrix,

      1. Row - Statuses from a dimension table
      2. Column - A calculated column of months in YYYY-MM format
      3. Measure - Above mentioned measure to display last 12 months data.

      Below is the data model,

       

      Relationships,

      1. Status code relationship between tblIndexHD and CFIndexstatus
      2. Date relationship with date table (for the measure).

      Thank you very much for your valuable time. 

  • aggisss's avatar
    aggisss
    Frequent Visitor
    3 years ago

    Hi,
    have you solced this issue?
    I have similar problem,
    even if I add filter on a visual for offset month is not working.
    Looks like function is blank, removes date filter.