Power BI Power query DAX query M

Power BI M functions

Posted by John Liu on Tuesday, August 6, 2024

Empty table

This query returns a table with a single text column called YourColumnName and no rows.

let
  Source = #table(
    type table [YourColumnName = text],
    {}
  )
in
  Source

Conditional Formatting

Highlight max and min

This function returns the color value for the Total Sales figure based on if the value is the max/min or other.

MaxMin =
VAR Sales = [Total Sales]
VAR MaxSalesOverall =
MAXX(
	ALLSELECTED('dimDate'[Year],'dimDate'[Quarter],'dimDate'[QuarterNo]),
	CALCULATE([Total Sales])
)
VAR MinSalesOverall = 
MINX(
	ALLSELECTED('dimDate'[Year],'dimDate'[Quarter],'dimDate'[QuarterNo]),
	CALCULATE([Total Sales])
)
VAR Result =
SWITCH(
	TRUE,
	Sales = MaxSalesOverall, "#22957e",
	Sales = MinSalesOverall, "#ff908c",
	"#7bc8fe"
)
RETURN
Result

Above Average

This function indicates if the Total Sales value is above average (1) or not (0).

AboveAvg =
VAR Sales = [Total Sales]
VAR AvgSalesOverall =
AVERAGEX(
	ALLEXCEPT(dimDate,'dimDate'[Date],'dimDate'[Day],'dimDate'[DayNo],'dimDate'[Month],'dimDate'[MonthNo]),
	CALCULATE([Total Sales])
)
VAR Result =
IF(
	Sales>AvgSalesOverall,
	1,
	0
)
RETURN
Result
CATALOG
    FEATURED TAGS
    ai api automation azure azure cosmos db azure openai azure vm base64 dax docker excel execution plan hyper-v infrastructure m machine learning machine learning services network network card performance power automate power bi power bi report tricks power query powershell python qgis query performance regex replication snowflake sql sql script sql server sql server admin sql server config ssl ssms troubleshooting unicode vmware