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.
I have a SummarizeColumns query which works fine but I would like to have and or logic in the Filter on 2 different dimensions. What I am trying to do is to have the logic where (tracking prefix = 016162 and person id = 5489552) or (tracking prefix = 016835). I could do it in 2 queries and merge them but I was trying to work out how to do it in one go. Any ideas?
EVALUATE
SUMMARIZECOLUMNS (
'Tracking Prefix'[Tracking Prefix],
Route[DC Id],
Company[Company Id],
FILTER (
ALL ( Route[DC Id] ),
CONTAINSROW (
//{ 191 },
{ 95, 89, 92, 96, 94, 90, 91, 321, 93, 191, 2180, 2575, 180 },
Route[DC Id]
)
),
FILTER ( Company, Company[Company Id] = 3222 ),
FILTER (
ALL ( 'Tracking Prefix' ),
CONTAINSROW ( { "016162","016835" }, 'Tracking Prefix'[Tracking Prefix] )
)
, FILTER ( Engineers, Engineers[Person Id] = 5489552 ),
FILTER (
'Date',
'Date'[Date]
>= DATE ( YEAR ( NOW () ) - 2, 01, 01 )
&& 'Date'[Date]
< DATE ( YEAR ( NOW () ), MONTH ( NOW () ), DAY ( NOW () ) ) --&& 'Date'[Current Week] <> "CurrentWeekByBox"
),
"Total Ops Deliveries", [Ops Total Deliveries],
)
Solved! Go to Solution.
Hi @gooranga1
You could use some examples from this page, particularly examples #12 or #13:
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
Using example #12, the DAX for that particular filter could look something like:
FILTER (
CROSSJOIN (
ALL ( 'Tracking Prefix'[Tracking Prefix] ),
ALL ( Engineers[Person Id] )
),
'Tracking Prefix'[Tracking Prefix]
IN { "016162", "016835" }
|| Engineers[Person Id] = 5489552
)
Regards,
Owen
Hi @gooranga1
You could use some examples from this page, particularly examples #12 or #13:
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
Using example #12, the DAX for that particular filter could look something like:
FILTER (
CROSSJOIN (
ALL ( 'Tracking Prefix'[Tracking Prefix] ),
ALL ( Engineers[Person Id] )
),
'Tracking Prefix'[Tracking Prefix]
IN { "016162", "016835" }
|| Engineers[Person Id] = 5489552
)
Regards,
Owen
Thanks @OwenAuger that's perfect. I had to replace the tables in the cross join with filtered calculate tables as both the tables are very large so the query took too long!
Thanks!
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |