Sort by column values with multiple fields in rows in matrix

askpbiuser — Fri, 01 Sep 2023 11:12:49 GMT

Hi, I need to display Country and Customer coming from different Dim tables and show them in rows in matrix visual. The field ( Product) in Columns in Matrix is from a different Dim table all connected to Fact table by one-to-many relationship.

The below measure works fine when I have either Country or Customer in rows, but as soon as I add both fields, the measure doesn't like it.
I need to basically highlight the minimum value for each Product column by using the below measure in Conditional formatting.
The measure I tried -

Background Measure =

VAR _1 = CALCULATETABLE(
ADDCOLUMNS
(SUMMARIZE('Fact','Dim Country'[Country], 'Dim Product'[Product]),
"@Amt", [Value]
),
ALLSELECTED(''Dim Product'[Product])
)
var MinValue = MINX(_1, [@Amt])
var MaxValue = MAXX(_1, [@Amt])
var CurrentValue = [Value]
var Result =
SWITCH(TRUE(),
CurrentValue = MinValue, 1,
CurrentValue = MaxValue, 2, BLANK())
RETURN Result

If I add Country to rows in matrix and customer coming from Dim Customer[Customer] to the above measure in summarize, it does not give right result. It only works when I have one field say Country in rows and product in columns.Could someone guide on what I am doing wrong? I need the below output when both Country and Customer are in rows.

Re: Sort by column values with multiple fields in rows in matrix

some_bih — Sun, 03 Sep 2023 19:41:35 GMT

Hi askpbiuser your part for SUMMARIZE define "later" output

SUMMARIZE('Fact','Dim Country'[Country], 'Dim Product'[Product])

combined with ALLSELECTED(''Dim Product'[Product]) which "missing" Dim Customer table / some column

Try to add to this part what you need, like customer.

Hope this help. Kudos appreciated.

topic Sort by column values with multiple fields in rows in matrix in DAX Commands and Tips

Sort by column values with multiple fields in rows in matrix

Re: Sort by column values with multiple fields in rows in matrix