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.
hi folks,
I am trying to do a dual filter on a table, running DirectQuery mode, which doesnt seem to work as I hoped - hoping someone here has better ideas.
Example table:
Outcome date | Outcome 1 | Outcome Show |
4/5/2019 | 56 | 700 |
4/9/2019 | 78 | 800 |
5/23/2019 | 250 | 900 |
7/30/2019 | 79 | 1000 |
7/30/2019 | 80 | 1100 |
what I am trying to do is a table that shows:
Outcome date | Outcome Show |
7/30/2019 | 1100 |
with this Measure:
Outcome Filter Measure = SUMX(
FILTER(
FILTER(
'Outcome Table',
'Outcome Table'[Outcome Date]=MAX('Outcome Table'[Outcome Date])
),
'Outcome Table'[Outcome 1]=MAX('Outcome Table'[Outcome 1])
),
'Outcome Table'[Outcome Show]*1
Which ends up showing up blank - because the outer filter doesnt seem to work - ie. DAX views them not as sequential filters, but as "AND" filters. So if dont do the second filter, and just filter for MAX Outcome Date, I end up with first row of Max date, showing 1000, while I want to have the table filtered first down to the Latest date availiable, then within that subset, look for the largest Outcome 1 value, then only show resulting row.
Thanks all!
Hi @VTB
Try the below.
Measure = VAR _date = CALCULATE( MAX( 'Outcome Table'[Outcome date] ), ALL('Outcome Table' ) ) VAR _one = CALCULATE( MAX( 'Outcome Table'[Outcome 1] ), 'Outcome Table'[Outcome date] = _date ) RETURN CALCULATE( SUM( 'Outcome Table'[Outcome Show] ) * 1, 'Outcome Table'[Outcome 1] = _one )
got the syntax to work, but returns blanks for every row.
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |