Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
@UsePowerBI Well, there is the Definitive Guide to DAX book, which is always good to have.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |