Forum Discussion

amaniramahi's avatar
amaniramahi
Icon for Helper V rankHelper V
6 years ago
Solved

Stop Page filter from one visual

Hi,

 

I have a page filter (InvoiceDate is on or after  1/1/2020)

The problem is there's  a visual to display the results in  2019 only (as I have records dated from 2017 - 2020) 

 

the measure = 

CALCULATE(COUNT(Sales[Vin]),FILTER(Sales,Sales[YTD Sales]="YTD"),FILTER(Sales,Sales[InvoiceDate].[Year]=2019))
 
but it always shows as 0
 
how can I solve this?
  • what is this filter : ,FILTER(Sales,Sales[YTD Sales]="YTD")

    You have to use all as dates are filter at page level

    CALCULATE(COUNT(Sales[Vin]),FILTER(all(Sales),Sales[InvoiceDate].[Year]=2019))

     

    You should prefer to use time intelligence with date calendar

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
    This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
    
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
    Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
    
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

     

  • Hi, amaniramahi 

     

    As is suggested by amitchandak , I created data to reproduce your scenario.

     

    ALL is a useful function that returns all the rows of a table or all the values of a column, depending on the parameter you use. In all of its variations, ALL ignores any existing filter to produce its result. You can use ALL as an argument of an iteration function, such as SUMX and FILTER, or as a filter argument in a CALCULATE function. You may create a measure as follows. 

     

    Result = 
    CALCULATE(
        COUNT('Table'[Value]),
        FILTER(
            ALL('Table'),
            YEAR('Table'[Date]) = 2019
        )
    )

     

     

    Result:

     

    Best Regards

    Allan

     

     

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

2 Replies

  • what is this filter : ,FILTER(Sales,Sales[YTD Sales]="YTD")

    You have to use all as dates are filter at page level

    CALCULATE(COUNT(Sales[Vin]),FILTER(all(Sales),Sales[InvoiceDate].[Year]=2019))

     

    You should prefer to use time intelligence with date calendar

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
    This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
    
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
    Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
    
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

     

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support

    Hi, amaniramahi 

     

    As is suggested by amitchandak , I created data to reproduce your scenario.

     

    ALL is a useful function that returns all the rows of a table or all the values of a column, depending on the parameter you use. In all of its variations, ALL ignores any existing filter to produce its result. You can use ALL as an argument of an iteration function, such as SUMX and FILTER, or as a filter argument in a CALCULATE function. You may create a measure as follows. 

     

    Result = 
    CALCULATE(
        COUNT('Table'[Value]),
        FILTER(
            ALL('Table'),
            YEAR('Table'[Date]) = 2019
        )
    )

     

     

    Result:

     

    Best Regards

    Allan

     

     

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