Power BI Power query DAX 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 availability availability-sets availability-zones aws-vm azure azure-automation-runbook azure-blob azure-cosmos-db azure-data-lake azure-deployment azure-function-app azure-functions azure-openai azure-sign-in azure-site-recovery azure-sql-database azure-subscription azure-vm base64 certificate change-data-capture change-tracking chrome clr container cte data-api-builder data-conversion data-gateway database-mail database-role database-size date-table dax db-config derived-table diagram direct-query disk-management disk-space docker downtime dtc dynamic-m-parameter embedding encrypted-connection excel excel-online excel-online-for-business execution-plan extended-events external-data fabric fabric-capacity failover-cluster fk geometry hierarchy httpwebrequest hugo hyper-v incognito-mode index infrastructure inline-tvf json kql lakehouse linked-server live-query-statistics locking m machine-learning machine-learning-model machine-learning-services master-key mcp mdx memory memory-grant mermaid mirrored-sql-server network network-card network-category office-script onedrive onnx-runtime openrowset p2v parquet performance polybase power-automate power-bi power-bi-report-tricks power-platform power-query powershell printer public-ip-address pyspark python qgis qt-designer query-performance query-plan query-troubleshooting r regex replication route s3 schema-design scripting self-signed-certificate server-role sharepoint snowflake software-development sofware-development spark sql sql-2025 sql-agent sql-availability-group sql-error sql-failover-cluster-instance sql-index sql-openjson sql-permission sql-recovery sql-script sql-security sql-server sql-server-admin sql-server-config sql-statistics ssis ssisdb ssl ssl/tls-error ssms table-expression tempdb terraform tips troubleshooting unicode view visual-studio visual-studio-code vmware wait-statistics wi-fi-connection-issue windows-settings