Ignore Slicer in measure

[bb72]

Probably the easiest question of today, had a look at the other questions on this but still struggling.

Trying to count the no of values (dates) in a column, but for this count to stay static regardless slicers etc then show how many values in the previous quarter.

My measures:

Total Values = CALCULATE(COUNTA('bi-import'[Values]))

Last Q = CALCULATE([Total Values], DATEADD('bi-import'[Values].[Date], -1, QUARTER))

However, my slicers are reducing the values/counts.

Where do I slot in the ALL function exactly for it to ignore the slicers? Thank you!

[stretcharm · Accepted solution]

Total Values = CALCULATE(COUNTA('bi-import'[Values]), ALL('bi-import'))

There are lots of blogs on the use of Filters in Dax.

e.g.

https://www.sqlbi.com/articles/filtering-tables/

http://sqlblog.com/blogs/marco_russo/archive/2010/04/05/all-allexcept-and-values-in-dax.aspx

https://docs.microsoft.com/en-us/power-bi/desktop-quickstart-learn-dax-basics

https://docs.microsoft.com/en-us/power-bi/guided-learning/introductiontodax#step-6

[v-sihou-msft · Accepted solution]

@bb72

If you just need to calculate those values for previous quarter, you can directly try PREVIOUSQUARTER() function in CALCULATE().

Previous Quarter Total Values =
CALCULATE (
    COUNTA ( 'bi-import'[Values] ),
    PREVIOUSQUARTER ( 'bi-import'[Date] )
)

Regards,

[stretcharm · Accepted solution]

Total Values = CALCULATE(COUNTA('bi-import'[Values]), ALL('bi-import'))

There are lots of blogs on the use of Filters in Dax.

e.g.

https://www.sqlbi.com/articles/filtering-tables/

http://sqlblog.com/blogs/marco_russo/archive/2010/04/05/all-allexcept-and-values-in-dax.aspx

https://docs.microsoft.com/en-us/power-bi/desktop-quickstart-learn-dax-basics

https://docs.microsoft.com/en-us/power-bi/guided-learning/introductiontodax#step-6