Active Contracts:=CALCULATE(COUNTROWS(Contracts);
FILTER(Contracts; ('Contracts'[StartDate] <= LASTDATE('Date table'[Date])
&& 'Contracts'[EndDate]>= FIRSTDATE('Date table'[Date]))))
Table1: DateTable (auto-generated with Power Query)... source=first date, last date becomes today
Table2: ContractTable (Contract, StartDate, EndDate)
No links/joins
Sources:
ExcelFile,
Count active contracts,
Auto-generate DateTable
----
Difference between SUM/SUMX/CALCULATE
Mes;= SUM( Tbl[Revenue] ) //Single Column
X-Formulas iterates on a Table and evaluates the expression for each row.
Revenue := SUMX( Sales; Sales[Price]*Sales[Quantity] )
Note: The columns must all be from the same table, or use Related if there is a relationship.
Cost1 := SUMX( Dati; Dati[Cost] * Dati[Quantity] )
Cost2 := SUMX( Dati; FILTER( Dati; Dati[Quantity]>10; [Cost] * Dati[Quantity] ) )
Picture: Row context or Filter context (from PivotTable) - link
Incorrect: Mes:= CALCULATE( SUM(...); Tbl[Col1] > Tbl[Col2] ) //Two col.
Correct : Mes:= CALCULATE( SUM(...); Tbl[Col1] > 10 ) //One col.
Correct : Mes:= CALCULATE( SUM(...); FILTER( Tbl; Tbl[Col1] > Tbl[Col2] )) //Two col.
Picture: slide40
Picture: slide48
--------
VALUES
Picture: slide47
----
RANKING FORMULA
--> Ranking: 1, 2, 3, ....
ALT.1: With EARLIER()
RankingOnUnitPrice =
COUNTROWS (
FILTER( ALL('Product');
'Product'[UnitPrice] > EARLIER ( 'Product'[UnitPrice]
)
) + 1
ALT.2: With VAR
RankingOnUnitPrice =
VAR
CurrentUnitPrice = 'Product'[UnitPrice]
RETURN
COUNTROWS (
FILTER( ALL('Product');
'Product'[UnitPrice] > CurrentUnitPrice
)
) + 1
Video: The original name of EARLIER() was OUTER(); it gives you access to the Outer row Context - the previous iteration.
ALL() --> Does not look at the
values, instead looks at
all rows.
----
DYNAMIC GROUPINGS
Picture: (source + excel-file)
ALT1: with VAR
"Variables also has the advantage of evaluating the expression once so the expressions that reference them should perform better.". Key formula SWITCH(TRUE())
NumberOrders =
VAR SumOrders =
CALCULATE ( DISTINCTCOUNT ( FactInternetSales[SalesOrderNumber] ) )
RETURN
SWITCH (
TRUE (),
SumOrders <= 4, FORMAT ( SumOrders, “General Number” ),
SumOrders >= 5 && SumOrders <= 10, “5-10”,
SumOrders > 10, “More than 10”
)
Alt2: without VAR
"If you don’t have a variable, you need to create a separate measure for CALCULATE ( DISTINCTCOUNT ( FactInternetSales[SalesOrderNumber] ) ) and use this measure so you don’t repeat the same formula in the SWITCH statement."
Slicers for value fields
--------
VAR & RETURN FORMULA
DAX now has variable support
----
CALCULATE
CALCULATE() is the
only formula that can modify/replace (an exisiting column filter) a Filter Context (
video). But not other column filters.
Example a table with Colour="Red", but not Size
Alt1: RedSales = CALCULATE( Product[SalesAmount]; Product[Color]="Red")
--> same amount on every row.
Alt2: RedSales = CALCULATE( Product[SalesAmount]; FILTER(Product; Product[Color]="Red"))
--> only on the red row.
Video
----
DISTICTCOUNT of a UNION
Measure = var a = UNION(Table2,Table3) return COUNTROWS(summarize(a,Table2[Col1]))
Resources consumed...
Time (CPU) : Calculated Measures
Space (Memory) : Calculated Dimensions (Columns)
Pareto
If, ~80% comes from 1 variable
--> >90% comes from 2 variables ?
---
Getting started with DAX Studio
