Trying to create a Measure to provide the modal result based on row context and column

Question

my data looks like this:

Date	Region	Country	Area Of Concern	Composite Risk Entry
10/23/87	LAT	Haiti	Operations	High
10/23/87	LAT	Angola	HR	Medium

What I am trying to do is construct a measure such that I can have the modal Composite Risk Entry or modal Region within the row and columns context of matrices and other visuals that I might create. Two examples of Matrices I would like to create are as follows:

Rows and Sub-row	Modal Risk Entry Q1	Modal Risk Entry Q2
Region ->Area of Concern

Rows and Sub-row	Modal Region Q1	Modal Region Q2
Area of Concern -> risk entry

Is what I would want to do possible? Is so and you have a solution, would you mind also explaining to me the logic of it as well. I am still learning DAX and the logic of how some of the formulas works escapes me.

Alexis Olson Alexis Olson · Accepted Answer · 2021-04-06T20:44:25

DAX Patterns suggests the following to calculate the mode of the column Data[Value].

Mode :=
MINX (
    TOPN (
        1,
        ADDCOLUMNS (
            VALUES ( Data[Value] ),
            "Frequency", CALCULATE ( COUNT ( Data[Value] ) )
        ),
        [Frequency],
        0
    ),
    Data[Value]
)

This returns the value which has the top frequency, which is one particular application of returning the top value ordered by another column (which can be done in many ways).

For your particular situation, instead of Data[Value], you'd use the name of the column you want to find the mode of (e.g. Table1[Region]).

To understand the measure, read from the inside out.

VALUES ( Table1[Region] ) is the list of distinct regions within the local filter context.
For each region, we add the Frequency as a new column defined by counting the rows associated with that region within the local filter context.
Now we have a table with two columns (Region and Frequency) and take the Top 1 row after sorting by Frequency.
Finally, we take MINX (MAXX would work too) on this single row (which has two columns) with Table1[Region] as the second argument specifying which column to return the min (or max) of.

Trying to create a Measure to provide the modal result based on row context and column

1 Answers