Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am trying to modify a calculated field and add a filter that will exclude certain opportunity numbers from the summation.
The formula works without the last filter, but I need to exclude certain records from the reporting.
Any help would be grealy appreciated!
Thanks,
Greg
CY # SQO (All) =
CALCULATE (
COUNT ( 'Marketing Snapshot'[SQODate] ),
FILTER (
ALL ( 'Marketing Snapshot'[SQOYear] ),
'Marketing Snapshot'[SQOYear] = [Max SQO Year]
),
FILTER (
ALL ( 'Marketing Snapshot'[DateStampMonth] ),
'Marketing Snapshot'[DateStampMonth] = [Max DateStampMonth]
),
FILTER (
ALL ( 'Marketing Snapshot'[OpportunityNumber] ),
'Marketing Snapshot'[OpportunityNumber] NOT IN {"O751700","O761393"})
Solved! Go to Solution.
Hi @gsed99
The negation of
<X> IN <Y>
is
NOT <X> IN <Y>
Also, since your 3rd filter argument within CALCULATE doesn't reference any measures etc, you can get rid of FILTER (ALL(...)) and get the same result.
This expression should work:
CY # SQO (All) = CALCULATE ( COUNT ( 'Marketing Snapshot'[SQODate] ), FILTER ( ALL ( 'Marketing Snapshot'[SQOYear] ), 'Marketing Snapshot'[SQOYear] = [Max SQO Year] ), FILTER ( ALL ( 'Marketing Snapshot'[DateStampMonth] ), 'Marketing Snapshot'[DateStampMonth] = [Max DateStampMonth] ), NOT 'Marketing Snapshot'[OpportunityNumber] IN { "O751700", "O761393" } )
Regards,
Owen
Hi @gsed99
The negation of
<X> IN <Y>
is
NOT <X> IN <Y>
Also, since your 3rd filter argument within CALCULATE doesn't reference any measures etc, you can get rid of FILTER (ALL(...)) and get the same result.
This expression should work:
CY # SQO (All) = CALCULATE ( COUNT ( 'Marketing Snapshot'[SQODate] ), FILTER ( ALL ( 'Marketing Snapshot'[SQOYear] ), 'Marketing Snapshot'[SQOYear] = [Max SQO Year] ), FILTER ( ALL ( 'Marketing Snapshot'[DateStampMonth] ), 'Marketing Snapshot'[DateStampMonth] = [Max DateStampMonth] ), NOT 'Marketing Snapshot'[OpportunityNumber] IN { "O751700", "O761393" } )
Regards,
Owen
hey @gsed99,
can't this be set in visual, page or report level filters? had a thought seeing your post but not sure if that will work for you.
hey @indhu,
I would actually need this to be done in the formula becuase my report is showing other metrics, so if I filter it on the visual, it will be limited those other metrics.
Thanks,
Greg
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |