DAX

 DAX Data Analysis Expressions

VARIABLES

Running Total = 
VAR MaxDateInFilterContext = MAX ( Dates[Date] )
VAR MaxYear = YEAR ( MaxDateInFilterContext )
VAR DatesLessThanMaxDate =
    FILTER (
        ALL ( Dates[Date], Dates[Calendar Year Number] ),
        Dates[Date] <= MaxDateInFilterContext
            && Dates[Calendar Year Number] = MaxYear
    )
VAR Result =
    CALCULATE (
        [Total Sales],
        DatesLessThanMaxDate
    )
RETURN
    Result

FORMAT Numbers
actual = if(sum[actual] >1000000, "FORMAT(SUM([actual], "#, ##M"), IF(SUM([actual]>=1000, "FORMAT(SUM(actual]), "#,,.0K"))

FORMAT(min(column, "0.0%")
FORMAT(min(column, "Percent")


eg. if matrix is filtered, 
IF(ISFILTERED(field], SELECTEDVALUE([column])

HASONEVALUE 
Valuecheck = if(HASONEVALUE([column], VALUES(field))


FILTER table by related field = united states and sumx salesamount_usd
= SUMX(FILTER( 'InternetSales_USD' , RELATED('SalesTerritory'[SalesTerritoryCountry]) <>"United States" ) ,'InternetSales_USD'[SalesAmount_USD])

SUMX
SUMX(<table>, <expression>)
Returns the sum of an expression evaluated for each row in a table
= SUMX(FILTER(InternetSales, InternetSales[SalesTerritoryID]=5),[Freight]) The above example first filters the table, InternetSales, on the expression, 'InternetSales[SalesTerritoryID] = 5`, and then returns the sum of all values in the Freight column. In other words, the expression returns the sum of freight charges for only the specified sales area.

AND, can also use &&
Demand =
    SUMX (
        FILTER (
            RELATEDTABLE ( Assignments ),
            AND (
                [AssignmentStartDate] <= [TimeByDay],
                [TimeByDay] <= [AssignmentFinishDate]
            )
        ),
        Assignments[Av Per Day]
    )


CALCULATETABLE, SUMMARIZE
Calculate Table with Summarize and Filter

Order Profile =
CALCULATETABLE (
    SUMMARIZE (
        'Sales Table',
        'Sales Table'[Order_Num_Key],
        Customer[Sector],
        "Total Value", SUM ( 'Sales Table'[Net Invoice Value] ),
        "Order Count", DISTINCTCOUNT ( 'Sales Table'[Order_Num_Key] )
    ),
    YEAR ( DimDate[Datekey] ) = YEAR ( TODAY () )
)

=
CALCULATE ( [Sales Amount], Dates[Year] IN { 2020, 2019, 2018 } )

USERELATIONSHIP Uses inactive relationship between tables

=
CALCULATE (
    [Sales Amount],
    Customer[Gender] = "Male",
    Products[Color] IN { "Green", "Yellow", "White" },
    USERELATIONSHIP ( Dates[Date], Sales[Due Date] ),
    FILTER ( ALL ( Dates ), Dates[Date] < MAX ( Dates[Date] ) )
)

CACLULATE FILTER(ALL) CALCULATE([mymeasure], FILTER(ALL([year], [year]=1), FILTER(ALL([quarter], [quarter = 1), FILTER(ALL[title] , [title] = "mytitlename] ) KEEPFILTERS CALCULATE([actualmeasure], ,KEEPFILTERS([title] = "mytitle"]) same as CALCULATE([actualmeasure], FILTER(table, [actualmeasure]= "mytitle")

keepfilters() is more efficient

SWITCH SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])
= SWITCH([Month], 1, "January", 2, "February", 3, "March", 4, "April" , 5, "May", 6, "June", 7, "July", 8, "August" , 9, "September", 10, "October", 11, "November", 12, "December" , BLANK() )

SWITCH with Measure

= SWITCH(TRUE(), 
        [measure] = "turnover", [turnover]
        [measure] = "Profit", "[Profit]

, BLANK()

)

Visuals

Check Filtered = ISFILTERED([column])

Dynamic Visual 

MakeTransparent = 
IF([check filtered], "FFFFF00" # returns transparent - note hex 7
"White")

Message = IF([check Filtered), "", "please select a row")

Dynamic Graph Title
Graph year title = selectedvalue([columnname])& " -  My graph Title"

ADDCOLUMNS
New Table>
Creates a new table and adds columns to it (and in this case also filters it) 

2013Sales = FILTER(ADDCOLUMNS(FactInternetSales, "Dates", FactInternetSales[OrderDate], "Sales2", SUM(FactInternetSales[SalesAmount])), FactInternetSales[OrderYear]=2013)

RANK by 2 Columns (calculated column)
Measure = RANKX( Filter(all('Table'), 'Table'[customer] = EARLIER('Table'[Customer])), 'Table'[Txn Number],,DESC, DENSE)

Creates a rank for each customer based on the txn number for each customer

ALLNONBLANKROWS - use for circular errors when joining tables (basically creates a table with no blank rows)

Table1 = CALCULATETABLE(
Var result = SELECTCOLUMNS(FILTER(ALLNOBLANKROWS('Table'), 'Table'[Txn Date, "Customer", 'Table'[customer])

 Return Result

Add Previous Date as calculated column (can be slow)
previous txn date = 
var currentDate = 'table'[txn date]
var currentCustomer = 'table'[customer]
return
CALCULATE(MAX('table'[txn date[, FILTER(ALL('table'), 'table[txn date] < currentDate

                    && 'table'[customer = currentCustomer

 ))