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 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
Anonymous Thanks for the edits. Taking a look now. Is your RETURN line supposed to read:
RETURN IF ( HasOneExpression, CalcHasOneFilter, CalcAll )?