DAX: Using filter and summarize in same Query

  • Article

In this SQL Query we are grouping Sales by Year and Color plus adding a filter of 5000

select CalendarYear,color,sum(SalesAmount) from [DimProduct] join FactInternetSales
on DimProduct.[ProductKey]=FactInternetSales.[ProductKey]
join DimDate
on dimdate.datekey=FactInternetSales.OrderDatekey
Group by CalendarYear,color
Having sum(salesamount)>5000
order by CalendarYear,color

image

 

Equivalent Dax of achieving same is:

evaluate( filter (summarize ('Internet Sales', 'Date'[Calendar Year],
'Product'[Color] ,"Sales Amount",sum('Internet Sales'[Sales Amount]))
,Calculate(sum('Internet Sales'[Sales Amount]))>5000))
order by 'Date'[Calendar Year],
'Product'[Color]

image