To find the mode in a column, the DAX calculates the most frequently occurring value by using functions like COUNTROWS, VALUES, and TOPN together to return the mode based on the data present in your model. This measure dynamically changes as per user-applied filters so as to reflect the changes in the data context.
Here is a brief procedure for creating a DAX measure for mode:
1. Countrows Function and the Values Function
Values: This returns a one-column table that gives the distinct values that are contained in the column specified.
COUNTROWS: This function returns the number of rows in that table, which can count how many times each value occurs.
2. Apply the TopN Function
TOPN: This is used to return the top-N from a table. You can use it to return the maximum frequency value by sorting based on the count of that value.
DAX Measure for Mode.
Mode Value =
VAR SummaryTable =
ADDCOLUMNS(
VALUES('YourTable'[YourColumn]),
"Frequency", CALCULATE(COUNTROWS('YourTable'), 'YourTable'[YourColumn] = EARLIER('YourTable'[YourColumn]))
)
VAR MaxFrequency =
MAXX(SummaryTable, [Frequency])
VAR Mode =
CALCULATE(
FIRSTNONBLANK('YourTable'[YourColumn], 1),
FILTER(SummaryTable, [Frequency] = MaxFrequency)
)
RETURN
Mode
Now let's expand that Measure's Explanation:
SummaryTable:
VALUES('YourTable'[YourColumn]): This gets the unique values in the column.
ADD COLUMNS: A column called Frequency is added to the distinct values, which counts the Frequency of the value by COUNTROWS.
EARLIER: Counts the occurrence of that value in the dataset - refers to that value of YourColumn in the row context of the current row.
MaxFrequency:
MAXX(SummaryTable, [Frequency]): It finds the highest Frequency (the most frequent value) in that summary table.
Mode:
FIRSTNONBLANK: Retrieves the first non-blank from the most repeated column value. In the end, it will return the most frequently occurring value that will return the first non-blank value.
RETURN Mode: Finally, this measure returns mode (most frequent value).
Key Points:
This measure is dynamic, i.e., it will get updated depending upon the context formed by slicers or filters in the report. When a user places a filter, the most frequent value shall change.
This measure works efficiently by counting the Frequency of each distinct individual by COUNTROWS and VALUES and finding the mode using the TOPN logic wrapped in a CALCULATE function.
