Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Count all function not influenced by date

Hi,

Hi,

 

I am trying to calculate the total lines backlog in the relating table not influenced by any date.

 

Backlog is a filter on OTD category. This DAX function which is active now (output in beneeth table):

Total_Lines_Backlog = CALCULATE(COUNT(PD_vs_shipped_date[Item_Number]), PD_vs_shipped_date[OTD_category] = "Backlog")

 

So what I want is that the total lines backlog is always calculated at the full total of the table, not influenced by any date or slicer, which is 108 lines. I tried to use the ALL function, but not successfully. Can anyone help?

 

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi Anonymous,

     

    You could have a try with the formula below.

     

    Total_Lines_Backlog =
CALCULATE (
    COUNT ( PD_vs_shipped_date[Item_Number] ),
    FILTER (
        ALL ( PD_vs_shipped_date ),
        PD_vs_shipped_date[OTD_category] = "Backlog"
    )
)

    If you still need help, please share some data sample and your desired output so that we can help further investigate on it?

     

    Best Regards,

    Cherry

  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Try this measure

     

    =CALCULATE(COUNT(PD_vs_shipped_date[Item_Number]),PD_vs_shipped_date[OTD_category] = "Backlog",ALL(Calendar))

     

    I have assumed that:

     

    1. There is a Calendar Table
    2. There is a relationship from the Date column of your PD_vs_shipped_date Table to the Date column of your Calendar Table
    3. In the Calendar Table, you have the YearMonthnumber column
    4. In your visual, you will drag the YearMonthnumber field from the Calendar Table

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    Hi Anonymous,

     

    You could have a try with the formula below.

     

    Total_Lines_Backlog =
CALCULATE (
    COUNT ( PD_vs_shipped_date[Item_Number] ),
    FILTER (
        ALL ( PD_vs_shipped_date ),
        PD_vs_shipped_date[OTD_category] = "Backlog"
    )
)

    If you still need help, please share some data sample and your desired output so that we can help further investigate on it?

     

    Best Regards,

    Cherry

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Try this measure

     

    =CALCULATE(COUNT(PD_vs_shipped_date[Item_Number]),PD_vs_shipped_date[OTD_category] = "Backlog",ALL(Calendar))

     

    I have assumed that:

     

    1. There is a Calendar Table
    2. There is a relationship from the Date column of your PD_vs_shipped_date Table to the Date column of your Calendar Table
    3. In the Calendar Table, you have the YearMonthnumber column
    4. In your visual, you will drag the YearMonthnumber field from the Calendar Table