Forum Discussion
Calcuate DAX help
I have two measures
Example:
One is calculate(sum(value), filter(Table,table.source="a")
Another measure is calculate (sum(value),filter( table.table.source="b")
these measures are displaying in tabluar report.
state - Measure 1 ,Measure2
Also ,at the same time . The user asking a slicer ( tablea.date),that should required in the report which should filter only Measure2 values ,not measure1 value.
How to avoid slicer - filter values that should not pass to measure1 values? What is the best DAX for calucating the measure 1 ?
Hi Anonymous ,
Using all will clear all context filtering conditions.
Choosing to use all or allexcept depends on the results you expect.
There is only one state where you can choose to use all.
Measure = CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[source]="a"))There are several states that can choose to use allexcept
Measure = CALCULATE(SUM('Table'[value]),FILTER(ALLEXCEPT('Table','Table'[state]),'Table'[source]="a"))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Anonymous - You will need to use ALLEXCEPT in order to exclude certain filters from affecting your measure. Would be easier to be specific with sample data and expected output:
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.allexecpt not working.
Measure1 -
Sample
Source Process Date Value State A Blank 20 01 B 03/30/2020 30 01 B 04/30/2020 40 01 Report: when slicer applied on Process Date (exmple value 04/30/2020) ,the report should show like below
the Source A values should not filtered out based on process date
Source state Value A 01 20 B 01 40 Source
Hi Anonymous ,
Using all will clear all context filtering conditions.
Choosing to use all or allexcept depends on the results you expect.
There is only one state where you can choose to use all.
Measure = CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[source]="a"))There are several states that can choose to use allexcept
Measure = CALCULATE(SUM('Table'[value]),FILTER(ALLEXCEPT('Table','Table'[state]),'Table'[source]="a"))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous ,
Is this problem sloved?
If not, please let me know.Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous , refer if these can help
Measure 1 new = calculate([Measure 1], all(tablea[Date]))
Measure 1 new = calculate([Measure 1], removefilters(tablea[Date]))Refer
https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandakTry use ALL(Table) with your measure 1. This will exclude any external filter applied on measure.
If you want certain external filters to be applied on measure then use ALLEXCEPT (Table, [filtercolumn1],[filtercolumn2]... etc)....
calculate(sum(value), filter(All(Table),table.source="a")
