In this post I would like to Publish few more DAX scenarios I have encountered.
Join Tables
We can use CROSSJOIN function on tables to use All Columns required by the SUMMARIZE function.
DEFINE
VAR JoinTable =
FILTER (
CROSSJOIN ( Products, FactSales ),
DimProducts[Id] = SalesFactSales[ProductId]
)
VAR Result Table=
SUMMARIZE (
JoinTable ,
DimProducts[Choice],
DimProducts[Id],
FactSales[StoreID],
FactSales[WeekId],
“Quantity”, SUMX (
FILTER ( ‘FactSales’, ‘DimProducts'[Id] = ‘FactSales'[ProductId] ),
FactSales[Quantity]
)
)
EVALUATE
ResultTable
SUMMARIZECOLUMNS
SUMMARIZECOLUMNS is recommended than SUMMARIZE because it is Faster & Optimized.
SUMMARIZE still exists for Backwordk Compatibility.
Let us see the Cross Join using SUMMARIZECOLUMNS.
Cross Join with Matching ProductID
DEFINE VAR Results =
SUMMARIZECOLUMNS (
‘DimProducts'[ProductID],
“Sales Amount”,
SUMX ( FILTER (
CROSSJOIN ( DimProducts, FactSales ),
DimProducts[ProductId] = FactSales[ProductId]
), FactSales[Quantity] * FactSales[Price] )
)
EVALUATE Results
Another way to see the results.
EVALUATE FILTER(CROSSJOIN (
DimProducts,
FactSales
),
DimProducts[ProductId] = FactSales[ProductId]
)
— Returns as many rows as in Sales table
Working on Table Variables
We can work on Table Variables to do more Aggregations. Example is given below.
For example to find Average, Use AVERAGEX function.
VAR ResultsTable =
ADDCOLUMNS (
SUMMARIZE (
ProductJoinSales,
DimProducts[Choice],
DimProducts[Id]
),
“CalcValue”, DIVIDE(
DIVIDE (
SUM (
FactSales[Quantity]
),
COUNT ( FactSales[StoreID] )
)
)
)
VAR Result = AVERAGEX (ResultsTable, [CalcValue])
Note Ensure to display the value using ROW function.
EVALUATE ROW(“Average”, Result)
OR Condition in Filter
Or Conditions in Filters are pretty easy. Use the || operator of C#.
FILTER ( FactSales, FactSales[Price] > 0 || FactSales[Quantity] > 0)
Reference
https://azure-arena.com/2018/08/03/my-dax-days/
https://azure-arena.com/2018/08/01/sql-tables-used-in-dax/