Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a fact table with a date column, a category column, and a numeric column. I want to filter the fact table for only the row that has the max date per category, and then sum up the values in the numeric column.
As can be seen in the picture below, the most recent dates are highlighted and the desired total comes up to 152.
I started by using SummarizeColumns to find the most recent date per category, but couldn't figure out how to filter the fact table and sum up the number column.
MyMeasure =
SUMMARIZECOLUMNS(
'FACT'[Category],
"Max Date", MAX('FACT'[Date])
)
Solved! Go to Solution.
@dpc_development
Add this measure:
MaxByCategory =
SUMX (
FILTER (
Fact,
VAR __V = CALCULATE ( MAX ( Fact[date] ), ALLEXCEPT ( Fact, Fact[category] ) ) RETURN
Fact[date] = __V
),
Fact[number]
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@dpc_development
Add this measure:
MaxByCategory =
SUMX (
FILTER (
Fact,
VAR __V = CALCULATE ( MAX ( Fact[date] ), ALLEXCEPT ( Fact, Fact[category] ) ) RETURN
Fact[date] = __V
),
Fact[number]
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I was able to get a solution like this, but not sure whether it is the best DAX code for this.
Remaining Stock =
SUMX(
SUMMARIZE(
'FACT',
[Category],
"Max Date", MAX('FACT'[Date])
),
LOOKUPVALUE('FACT'[NumericColumn],'FACT'[Category], [Category],'FACT'[Date], [Max Date])
)
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |