Forum Discussion
How to filter properly?
Edited per Greg_Deckler suggestion. Thank you
Hello,
I have a report that I am attempting to provide the users with two seamlingly simple a simple [Count] and [Modified Count] measues. The [Count] measure has no filtering applied and would display whatever selections have been made by the user. The [Modified Count] field would have the starting population remain fixed, unless the user applies a filter to Field.1 or Field.2.
I provided my current DAX expression for [Modified Count] and it does allow me to either modify the starting population by either Field.1 or Field.2, but the problem is that if i were to apply a filter in say Field.3 or Field.4 the starting population is also impacted(which is undesired).
Also, for contect:
- I want the date selected to remain no matter what is selected. That is accounted for using the Values function.
- I am using the hasonevalue function to identify when Field.1 and Field.2 are selected.
Is someone able to recommend a solution for me to attempt to fix this?
Thank you!
f_primary table below. Assume this is a giant flat file that I havent created any relationship tables.
| Item.ID | Field.1 | Field.2 | Field.3 | Field.4 | Date | Count |
001 | Blue | Dogs | PA | House | 1/1/2019 | 10 |
| 002 | Blue | Dogs | CA | House | 4/1/2019 | 100 |
| 003 | Red | Dogs | TX | Condo | 10/1/2019 | 5 |
| 004 | Blue | Dogs | MD | Condo | 11/1/2019 | 10 |
| 005 | Red | Cats | VA | House | 11/1/2019 | 75 |
[ModifiedCount] =
VAR Metric = Sum(Count)
VAR HasOneExpression = OR ( HASONEVALUE ( f_primary[Field.1] ), HASONEVALUE ( f_primary[Field.2] ) )
VAR CalcHasOneFilter = CALCULATE ( Metric, ALLEXCEPT ( f_primary, f_primary[Field.1], f_primary[Field.2] ), VALUES ( DateTbl ) ) VAR CalcAll = CALCULATE ( Metric, ALL ( f_primary ), VALUES ( DateTbl ) )
RETURN IF ( HasOneActual, CalcHasOneFilter, CalcAll )
Scenarios I wish to see:
1) if the User has Field.4 = House. I should see the following:
[Count] = 185
[Modified Count] = 200
2) if the User has Field.4 = House, and Field.1 = Blue. I should see the following:
[Count] = 110
[Modified Count] = 130
3) if the User has Field.1 = Blue, and Field.2 = Dogs, and Field.3=PA. I should see the following:
[Count] = 10
[Modified Count] = 120
Anonymous - Assuming the answer to the previous question is "Yes" the modified code is below. Essentially you had it correct except that you cannot recalculate a variable, which is what you were trying to do. This is why you were getting the incorrect result.
ModifiedCount = VAR Metric = SUM('f_primary'[Count]) VAR HasOneExpression = OR ( HASONEVALUE ( 'f_primary'[Field.1] ), HASONEVALUE ( 'f_primary'[Field.2] ) ) VAR CalcHasOneFilter = CALCULATE ( SUM('f_primary'[Count]), ALLEXCEPT ( 'f_primary', 'f_primary'[Field.1], 'f_primary'[Field.2], 'DateTbl'[Date] ) ) VAR CalcAll = CALCULATE ( SUM('f_primary'[Count]), ALLEXCEPT(f_primary, DateTbl[Date])) RETURN IF ( HasOneExpression, CalcHasOneFilter, CalcAll )
7 Replies
- "- I am using the hasonevalue function to identify when Field.1 and Field.2 are selected." To check if something has been selected from a column, it's best to use ISFILTERED, not HASONEVALUE. Actually, to see if only one value ahs been selected, you should use a combination of the two functions above.
And then again... Please read this to see why you should not do what you do above: https://www.sqlbi.com/articles/understanding-dax-auto-exist/
Best
D - Greg_Deckler
Community Champion
Sounds like an ALLEXCEPT kind of situation. But, difficult to be sure with the information provided. 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
- Greg_Deckler
Anonymous Thanks for the edits. Taking a look now. Is your RETURN line supposed to read:
RETURN IF ( HasOneExpression, CalcHasOneFilter, CalcAll )?