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.
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
Solved! Go to Solution.
@jamesrr25 - 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 )
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...
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
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!
@jamesrr25 Thanks for the edits. Taking a look now. Is your RETURN line supposed to read:
@jamesrr25 - 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 )
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
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |