SQL to DAX – FILTER, Customized Measures

SQL Statements

–1. Filter result on female customer shopping behavior
SELECT CalendarYear,
MonthNumberOfYear,
SUM(F.SalesAmount) AS TotalSales
FROM dbo.DimDate AS D
LEFT JOIN dbo.FactInternetSales AS F ON D.DateKey = F.OrderDateKey
LEFT JOIN dbo.DimCustomer AS C ON F.CustomerKey = C.CustomerKey
WHERE C.Gender = ‘F’
GROUP BY CalendarYear,
MonthNumberOfYear
HAVING SUM(F.SalesAmount) IS NOT NULL;

–2 Define calculate – female sales percentage
SELECT CalendarYear,
MonthNumberOfYear,
SUM(F.SalesAmount) AS TotalInternetSales,
SUM(CASE
WHEN c.gender = ‘F’
THEN F.SalesAmount
ELSE 0
END) AS TotalFemaleInternetSales,
CAST(CAST((SUM(CASE
WHEN c.gender = ‘F’
THEN F.SalesAmount
ELSE 0
END)/SUM(F.SalesAmount))*100 AS DECIMAL(18, 2)) AS VARCHAR(5))+’ %’ AS FemaleInternetSalesPerc
FROM dbo.DimDate AS D
LEFT JOIN dbo.FactInternetSales AS F ON D.DateKey = F.OrderDateKey
LEFT JOIN dbo.DimCustomer AS C ON F.CustomerKey = C.CustomerKey
GROUP BY CalendarYear,
MonthNumberOfYear
HAVING SUM(CASE
WHEN c.gender = ‘F’
THEN F.SalesAmount
ELSE 0
END)/SUM(F.SalesAmount) IS NOT NULL;

Corresponding DAX Statements

–1. Filter result based on female customer shopping behavior
–code one
EVALUATE
CALCULATETABLE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] ),
“TotalSales”, CALCULATE ( SUM ( FactInternetSales[SalesAmount] ) )
),
NOT ( ISBLANK ( [TotalSales] ) )
),
DimCustomer[Gender] = “F”
)
— code one is the same as this one
EVALUATE
CALCULATETABLE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] ),
“TotalSales”, CALCULATE ( SUM ( FactInternetSales[SalesAmount] ) )
),
NOT ( ISBLANK ( [TotalSales] ) )
),
filter(all(DimCustomer[Gender]),DimCustomer[Gender] = “F”)
)
— differnt behavior than code one
EVALUATE
CALCULATETABLE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] ),
“TotalSales”, CALCULATE ( SUM ( FactInternetSales[SalesAmount] ) )
),
NOT ( ISBLANK ( [TotalSales] ) )
),
filter(va(DimCustomer[Gender]),DimCustomer[Gender] = “F”)
)

–2, Define Measure and reuse, female sales percentage
DEFINE
MEASURE FactInternetSales[TotalInternetSales] =
SUM ( FactInternetSales[SalesAmount] )
MEASURE FactInternetSales[TotalFemaleInternetSales] =
CALCULATE ( SUM ( FactInternetSales[SalesAmount] ), DimCustomer[Gender] = “F” )
MEASURE FactInternetSales[FemaleSalesPerc] =
DIVIDE ( [TotalFemaleInternetSales], [TotalInternetSales], 0 )
EVALUATE
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] ),
“TotalInternetSales”, [TotalInternetSales],
“TotalFemaleInternetSales”, [TotalFemaleInternetSales],
“FemaleSalesPercent”, FORMAT ( [FemaleSalesPerc], “Percent” )
),
[FemaleSalesPerc] <> 0
)