Matrix. Conditional format.

[Syndicate_Admin]

I have a problem with an array and conditional formatting.

I have moved from powerbi to excel my case to tell it better.

I want to highlight in blue for each reception center, which is the animal that they have the most.

I'm really stuck and I can't finish this task at work.

Thanks a lot.

Greetings

[PaulDBrown]

What's the structure of the data table?

[Syndicate_Admin]

The table has more than 20 columns.

[PaulDBrown]

Try creating a new table visual with the field "Departamento al que pertenece o al que esta adscrito" and the following equivalent measure:

Max por Centro = MAXX(ALLEXCEPT('Table', 'Table'[Centro]), [Num Animales])

In my example, you get this:

[Syndicate_Admin]

Thank you very much for all this great help but I can't figure it out.

In this link you can see a copy of my power bi: https://we.tl/t-pWNDfTrhnY

Maybe you see the best solution as well.

[PaulDBrown · Accepted solution]

OK. Got it. Here is a solution.

1) Create a (new) dimension table for the "Departamento al que pertenece o al que está adscrito" using:

Dim Departemento = DISTINCT('Tabla madre'[Departamento al que pertenece o al que está adscrito])

Create a 1:N relationship between the corresponding fields in the tables:

Now create the following measures:

Num Filas = COUNT('Tabla madre'[Unidad a la que pertenece:])

And for the conditional formatting:

Condicion Pregunta1 =
IF (
    MAXX (
        ADDCOLUMNS (
            SUMMARIZE (
                ALL ( 'Tabla madre' ),
                'Tabla madre'[Departamento al que pertenece o al que está adscrito],
                'Tabla madre'[Unidad a la que pertenece:]
            ),
            "mx", [Num Filas]
        ),
        [mx]
    ) = [Num Filas],
    1
)

Create the table visual using the field from the Dim Departemento table for the rows, the Tabla Madre [Undidad a la que pertenece] as columns, add the [Num filas] as values, and set the conditional formatting with the corresponding measure. And you get this:

I've attached your sample file

[Syndicate_Admin]

Thank you very much, this was really advanced for my level. Thank you very much.

[PaulDBrown]

yea, sometimes DAX gets tricky. I'm glad we worked it out.

If the problem has been solved, can you please mark the relevant post as the solution? This will close the thread and help others looking for a similar solution. Thank you.

[Syndicate_Admin]

[amitchandak]

@Syndicate_Admin , if these three are measures or columns (Then on their measure), Create the following color measure and use them in conditional formatting using field value option

color Perro = if( [Perro] >[Gato] && [Perro] > [Iguanas], "Blue", "White")

color Gato = if( [Perro] <[Gato] && [Gato] > [Iguanas], "Blue", "White")

color Iguanas = if( [Iguanas] >[Gato] && [Perro] < [Iguanas], "Blue", "White")

PowerBI Abstract Thesis: How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

[Syndicate_Admin]

Dog, Cat and Iguana are inside a column called Animal.

[Syndicate_Admin]

[PaulDBrown]

Try this.

Measure to count animals by centro:

Num Animales = COUNTROWS('Table')

Measure to use in the conditional formatting interface:

Condit Format = 
IF(MAXX(ALLEXCEPT('Table','Table'[Centro]), [Num Animales]) = [Num Animales], 1)

[Syndicate_Admin]

Thank you very much for your help.

I have applied what you have told me and in the example of dogs and cats it works perfectly but when I use it in official work it does not work.

I show you in images.

[PaulDBrown]

Change the "Porcentaje" to "Número"

[Syndicate_Admin]

I get the same result 😞

[PaulDBrown]

You haven't changed it. The parameter is still "Porcentaje". Change it to "Número"

[Syndicate_Admin]

I've tried with number and neither.

[Syndicate_Admin]

The array is configured as follows:

[Syndicate_Admin]

Extra information: in my Mother Table, where I have all the information I have multiple columns (approx. about 20). Could that be because of that?