Using Filters

[UsePowerBI]

Hello

Is there a complete tutorial/guide on 'filtering' in PowerBI/DAX? Apart from the obvious visual filters?

I am figuring out how to use them (e.g. how to make All Page visual filters not to apply to a Measure with CALCULATE) but I then forget because it's not very straightforward.

Is there a definite and complete guide that I can refer to?

Thanks!

[v-rzhou-msft · Accepted solution]

Hi @UsePowerBI ,

Here I will answer your question and give you some advise.

e.g. how to make All Page visual filters not to apply to a Measure with CALCULATE

Try ALL function, it will remove all filter from your report, and your measure won't be impacted by report filter / slicer.

Measure looks like as below.

Measure =
CALCULATE(
	SUM('Table'[Data]),
	ALL('Table')
)

You may refer to  the blog or video as below to learn more details.

How to use ALL, ALLSELECTED, ALLEXCEPT, ALLNOBLANKROW in DAX

ALL, ALLSELECTED and ALLEXCEPT

Best Regards,
Rico Zhou

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

[Greg_Deckler]

@UsePowerBI Well, there is the Definitive Guide to DAX book, which is always good to have. 

[UsePowerBI]

For example, this does not seem to work:

KPI = DISTINCTCOUNT(A[Column1])&" out of "&CALCULATE(DISTINCTCOUNT(A[Column1]),ALL(A[Column5])>TODAY()-7)

The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.