DAX CHEAT SHEET - Make Your Power BI Report Look Like an App (5 Design Moves)
PK: An Excel Expert  |  www.pk-anexcelexpert.com  |  Trailhead Outfitters practice data
====================================================================================================

MEASURES ALREADY IN THE PRACTICE FILE (table _Measures)
----------------------------------------------------------------------------------------------------
Total Sales = SUM ( Sales[Sales] )

Total Cost = SUM ( Sales[Cost] )

Gross Profit = [Total Sales] - [Total Cost]

Margin % = DIVIDE ( [Gross Profit], [Total Sales] )

Orders = DISTINCTCOUNT ( Sales[Order ID] )

Units = SUM ( Sales[Quantity] )

Avg Order Value = DIVIDE ( [Total Sales], [Orders] )

Total Target = SUM ( Targets[Target] )

Sales vs Target % = DIVIDE ( [Total Sales], [Total Target] )

Sales PY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

YoY % = DIVIDE ( [Total Sales] - [Sales PY], [Sales PY] )


MOVE 3 - THE DYNAMIC WELCOME LINE
----------------------------------------------------------------------------------------------------
Modeling > New measure. Show it in a Card visual: Category label Off, Background Off.

Welcome Line =
VAR _Hour = HOUR ( NOW () )
VAR _Greeting =
    SWITCH ( TRUE (), _Hour < 12, "Good morning", _Hour < 17, "Good afternoon", "Good evening" )
VAR _Year =
    IF ( HASONEVALUE ( 'Date'[Year] ), FORMAT ( VALUES ( 'Date'[Year] ), "0" ), "All years" )
VAR _Regions =
    IF (
        ISFILTERED ( Regions[Region] ),
        CONCATENATEX ( VALUES ( Regions[Region] ), Regions[Region], ", " ),
        "All regions"
    )
VAR _AsOf = FORMAT ( CALCULATE ( MAX ( Sales[Order Date] ), REMOVEFILTERS () ), "mm/dd/yyyy" )
RETURN
    _Greeting & ", Sales Team!   " & _Year & "  |  " & _Regions & "  |  Data through " & _AsOf


MOVE 4 - SVG KPI CARDS
----------------------------------------------------------------------------------------------------
Modeling > New measure, paste, then Measure tools > Data category = Image URL.
Show it in an Image visual: Format > Image > Image URL > fx > Field value > the measure.
Same pattern for every card - only the label, the base measure and the number format change.

KPI Card Sales =
VAR _Value = [Total Sales]
VAR _PY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR _Change = DIVIDE ( _Value - _PY, _PY )
VAR _Color = IF ( _Change >= 0, "#1E9E5A", "#D64545" )
VAR _Arrow = IF ( _Change >= 0, "▲", "▼" )
VAR _Shown = IF ( _Value >= 1000000, FORMAT ( _Value / 1000000, "$#,##0.00" ) & "M", FORMAT ( _Value / 1000, "$#,##0.0" ) & "K" )
-- sparkline: one point per month in the current filter context
VAR _Months = ADDCOLUMNS ( VALUES ( 'Date'[Month Index] ), "@v", [Total Sales] )
VAR _MinI = MINX ( _Months, 'Date'[Month Index] )
VAR _MaxI = MAXX ( _Months, 'Date'[Month Index] )
VAR _MinV = MINX ( _Months, [@v] )
VAR _MaxV = MAXX ( _Months, [@v] )
VAR _Points =
    CONCATENATEX (
        _Months,
        FORMAT ( 172 + DIVIDE ( 'Date'[Month Index] - _MinI, _MaxI - _MinI ) * 92, "0.0" ) & ","
            & FORMAT ( 66 - DIVIDE ( [@v] - _MinV, _MaxV - _MinV ) * 34, "0.0" ),
        " ",
        'Date'[Month Index], ASC
    )
VAR _Svg =
    "<svg xmlns='http://www.w3.org/2000/svg' width='280' height='104' viewBox='0 0 280 104'>"
        & "<rect x='1' y='1' width='278' height='102' rx='12' fill='#FFFFFF' stroke='#E3E8EF'/>"
        & "<rect x='0' y='20' width='5' height='64' rx='2.5' fill='#F5A623'/>"
        & "<text x='22' y='30' font-family='Segoe UI' font-size='12' font-weight='600' fill='#6B7A90' letter-spacing='1'>TOTAL SALES</text>"
        & "<text x='22' y='64' font-family='Segoe UI Semibold' font-size='28' fill='#13395D'>" & _Shown & "</text>"
        & "<rect x='22' y='74' width='142' height='20' rx='10' fill='" & _Color & "' fill-opacity='0.12'/>"
        & "<text x='31' y='88' font-family='Segoe UI Semibold' font-size='11' fill='" & _Color & "'>" & _Arrow & " " & FORMAT ( ABS ( _Change ), "0.0%" ) & " vs last year" & "</text>"
        & "<polyline points='" & _Points & "' fill='none' stroke='#2E6DA4' stroke-width='2.5' stroke-linecap='round' stroke-linejoin='round'/>"
        & "</svg>"
-- inside a data URI, # starts a fragment and % starts an escape: encode both (% first)
RETURN
    "data:image/svg+xml;utf8," & SUBSTITUTE ( SUBSTITUTE ( _Svg, "%", "%25" ), "#", "%23" )


KPI Card Profit =
VAR _Value = [Gross Profit]
VAR _PY = CALCULATE ( [Gross Profit], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR _Change = DIVIDE ( _Value - _PY, _PY )
VAR _Color = IF ( _Change >= 0, "#1E9E5A", "#D64545" )
VAR _Arrow = IF ( _Change >= 0, "▲", "▼" )
VAR _Shown = IF ( _Value >= 1000000, FORMAT ( _Value / 1000000, "$#,##0.00" ) & "M", FORMAT ( _Value / 1000, "$#,##0.0" ) & "K" )
-- sparkline: one point per month in the current filter context
VAR _Months = ADDCOLUMNS ( VALUES ( 'Date'[Month Index] ), "@v", [Gross Profit] )
VAR _MinI = MINX ( _Months, 'Date'[Month Index] )
VAR _MaxI = MAXX ( _Months, 'Date'[Month Index] )
VAR _MinV = MINX ( _Months, [@v] )
VAR _MaxV = MAXX ( _Months, [@v] )
VAR _Points =
    CONCATENATEX (
        _Months,
        FORMAT ( 172 + DIVIDE ( 'Date'[Month Index] - _MinI, _MaxI - _MinI ) * 92, "0.0" ) & ","
            & FORMAT ( 66 - DIVIDE ( [@v] - _MinV, _MaxV - _MinV ) * 34, "0.0" ),
        " ",
        'Date'[Month Index], ASC
    )
VAR _Svg =
    "<svg xmlns='http://www.w3.org/2000/svg' width='280' height='104' viewBox='0 0 280 104'>"
        & "<rect x='1' y='1' width='278' height='102' rx='12' fill='#FFFFFF' stroke='#E3E8EF'/>"
        & "<rect x='0' y='20' width='5' height='64' rx='2.5' fill='#F5A623'/>"
        & "<text x='22' y='30' font-family='Segoe UI' font-size='12' font-weight='600' fill='#6B7A90' letter-spacing='1'>GROSS PROFIT</text>"
        & "<text x='22' y='64' font-family='Segoe UI Semibold' font-size='28' fill='#13395D'>" & _Shown & "</text>"
        & "<rect x='22' y='74' width='142' height='20' rx='10' fill='" & _Color & "' fill-opacity='0.12'/>"
        & "<text x='31' y='88' font-family='Segoe UI Semibold' font-size='11' fill='" & _Color & "'>" & _Arrow & " " & FORMAT ( ABS ( _Change ), "0.0%" ) & " vs last year" & "</text>"
        & "<polyline points='" & _Points & "' fill='none' stroke='#2E6DA4' stroke-width='2.5' stroke-linecap='round' stroke-linejoin='round'/>"
        & "</svg>"
-- inside a data URI, # starts a fragment and % starts an escape: encode both (% first)
RETURN
    "data:image/svg+xml;utf8," & SUBSTITUTE ( SUBSTITUTE ( _Svg, "%", "%25" ), "#", "%23" )


KPI Card Margin =
VAR _Value = [Margin %]
VAR _PY = CALCULATE ( [Margin %], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR _Change = _Value - _PY
VAR _Color = IF ( _Change >= 0, "#1E9E5A", "#D64545" )
VAR _Arrow = IF ( _Change >= 0, "▲", "▼" )
VAR _Shown = FORMAT ( _Value, "0.0%" )
-- sparkline: one point per month in the current filter context
VAR _Months = ADDCOLUMNS ( VALUES ( 'Date'[Month Index] ), "@v", [Margin %] )
VAR _MinI = MINX ( _Months, 'Date'[Month Index] )
VAR _MaxI = MAXX ( _Months, 'Date'[Month Index] )
VAR _MinV = MINX ( _Months, [@v] )
VAR _MaxV = MAXX ( _Months, [@v] )
VAR _Points =
    CONCATENATEX (
        _Months,
        FORMAT ( 172 + DIVIDE ( 'Date'[Month Index] - _MinI, _MaxI - _MinI ) * 92, "0.0" ) & ","
            & FORMAT ( 66 - DIVIDE ( [@v] - _MinV, _MaxV - _MinV ) * 34, "0.0" ),
        " ",
        'Date'[Month Index], ASC
    )
VAR _Svg =
    "<svg xmlns='http://www.w3.org/2000/svg' width='280' height='104' viewBox='0 0 280 104'>"
        & "<rect x='1' y='1' width='278' height='102' rx='12' fill='#FFFFFF' stroke='#E3E8EF'/>"
        & "<rect x='0' y='20' width='5' height='64' rx='2.5' fill='#F5A623'/>"
        & "<text x='22' y='30' font-family='Segoe UI' font-size='12' font-weight='600' fill='#6B7A90' letter-spacing='1'>PROFIT MARGIN</text>"
        & "<text x='22' y='64' font-family='Segoe UI Semibold' font-size='28' fill='#13395D'>" & _Shown & "</text>"
        & "<rect x='22' y='74' width='142' height='20' rx='10' fill='" & _Color & "' fill-opacity='0.12'/>"
        & "<text x='31' y='88' font-family='Segoe UI Semibold' font-size='11' fill='" & _Color & "'>" & _Arrow & " " & FORMAT ( ABS ( _Change ) * 100, "0.0" ) & " pts vs last year" & "</text>"
        & "<polyline points='" & _Points & "' fill='none' stroke='#2E6DA4' stroke-width='2.5' stroke-linecap='round' stroke-linejoin='round'/>"
        & "</svg>"
-- inside a data URI, # starts a fragment and % starts an escape: encode both (% first)
RETURN
    "data:image/svg+xml;utf8," & SUBSTITUTE ( SUBSTITUTE ( _Svg, "%", "%25" ), "#", "%23" )


KPI Card Orders =
VAR _Value = [Orders]
VAR _PY = CALCULATE ( [Orders], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR _Change = DIVIDE ( _Value - _PY, _PY )
VAR _Color = IF ( _Change >= 0, "#1E9E5A", "#D64545" )
VAR _Arrow = IF ( _Change >= 0, "▲", "▼" )
VAR _Shown = FORMAT ( _Value, "#,##0" )
-- sparkline: one point per month in the current filter context
VAR _Months = ADDCOLUMNS ( VALUES ( 'Date'[Month Index] ), "@v", [Orders] )
VAR _MinI = MINX ( _Months, 'Date'[Month Index] )
VAR _MaxI = MAXX ( _Months, 'Date'[Month Index] )
VAR _MinV = MINX ( _Months, [@v] )
VAR _MaxV = MAXX ( _Months, [@v] )
VAR _Points =
    CONCATENATEX (
        _Months,
        FORMAT ( 172 + DIVIDE ( 'Date'[Month Index] - _MinI, _MaxI - _MinI ) * 92, "0.0" ) & ","
            & FORMAT ( 66 - DIVIDE ( [@v] - _MinV, _MaxV - _MinV ) * 34, "0.0" ),
        " ",
        'Date'[Month Index], ASC
    )
VAR _Svg =
    "<svg xmlns='http://www.w3.org/2000/svg' width='280' height='104' viewBox='0 0 280 104'>"
        & "<rect x='1' y='1' width='278' height='102' rx='12' fill='#FFFFFF' stroke='#E3E8EF'/>"
        & "<rect x='0' y='20' width='5' height='64' rx='2.5' fill='#F5A623'/>"
        & "<text x='22' y='30' font-family='Segoe UI' font-size='12' font-weight='600' fill='#6B7A90' letter-spacing='1'>ORDERS</text>"
        & "<text x='22' y='64' font-family='Segoe UI Semibold' font-size='28' fill='#13395D'>" & _Shown & "</text>"
        & "<rect x='22' y='74' width='142' height='20' rx='10' fill='" & _Color & "' fill-opacity='0.12'/>"
        & "<text x='31' y='88' font-family='Segoe UI Semibold' font-size='11' fill='" & _Color & "'>" & _Arrow & " " & FORMAT ( ABS ( _Change ), "0.0%" ) & " vs last year" & "</text>"
        & "<polyline points='" & _Points & "' fill='none' stroke='#2E6DA4' stroke-width='2.5' stroke-linecap='round' stroke-linejoin='round'/>"
        & "</svg>"
-- inside a data URI, # starts a fragment and % starts an escape: encode both (% first)
RETURN
    "data:image/svg+xml;utf8," & SUBSTITUTE ( SUBSTITUTE ( _Svg, "%", "%25" ), "#", "%23" )


MOVE 5 - FIELD PARAMETER DATE SWITCHER
----------------------------------------------------------------------------------------------------
Modeling > New parameter > Fields. Name: Date Level. Add Date[Month Year], Date[Year Quarter], Date[Year]
in that order (rename them Month / Quarter / Year - the first row is the default), keep 'Add slicer to this page' ticked. Power BI writes:

Date Level =
{
    ("Month", NAMEOF ( 'Date'[Month Year] ), 0),
    ("Quarter", NAMEOF ( 'Date'[Year Quarter] ), 1),
    ("Year", NAMEOF ( 'Date'[Year] ), 2)
}

Put 'Date Level' on the X-axis of the trend chart instead of Month Year, then make the slicer
a single-select button row: Slicer settings > Style = Tile, Single select = On.
