topic Re: How to filter properly? in DAX Commands and Tips

How to filter properly?

Anonymous — Sun, 15 Mar 2020 13:28:29 GMT

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

Re: How to filter properly?

Greg_Deckler — Sun, 15 Mar 2020 03:30:20 GMT

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

Re: How to filter properly?

Anonymous — Sun, 15 Mar 2020 13:29:41 GMT

Thank you for the suggested posting guidelines. I attempted to modify my post to make this more clear. I have searched around for this answer in the community and have watched many youtube/sqlbi clips and cant figure out what I am doiung wrong. Thank you for any assistance you are able to provide!

Re: How to filter properly?

Greg_Deckler — Sun, 15 Mar 2020 14:07:24 GMT

Anonymous Thanks for the edits. Taking a look now. Is your RETURN line supposed to read:

RETURN IF ( HasOneExpression, CalcHasOneFilter, CalcAll )

Re: How to filter properly?

Greg_Deckler — Sun, 15 Mar 2020 14:40:00 GMT

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 )

Re: How to filter properly?

Anonymous — Sun, 15 Mar 2020 15:06:49 GMT

"- 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

Re: How to filter properly?

Anonymous — Sun, 15 Mar 2020 21:49:07 GMT

Thank you sir! I will test this out in a bit and see how it works. I knew i shouldnt have done that with the recalculated measure. I originally did that because I have to recreate this measure for other calculations and thought I could get by with copy/pasting the calculation and just changing the metric used at the top. I'll test and come back if this actually works to 'mark as solution'. Thank you

Re: How to filter properly?

Anonymous — Sun, 15 Mar 2020 22:00:46 GMT

Thank you Anonymous. I will check that out today as well. Apparently my search was not inclusive enough. I really need to finish their DAX Masterclass...