Help with the graphic

[Anonymous]

Hi!

I am having a problem with the graphic of some datas.

First, I have the following table:

Where:

METAS1 = IF([MTTR DIC]>VALUE(META_MTTR[MTTR MAX]);"INEFICIENTE";IF([MTTR DIC]<VALUE([MTTR PROM]);"EXCEDE";IF(AND([MTTR DIC]<VALUE([MTTR MAX]);[MTTR DIC]>VALUE([MTTR PROM]));"CUMPLE META";"n/d")))

And the problem is than I can´t make any graphic with these datas except the table I show above, and I know the problem is with the measure  METAS1 and I don´t know why. (I am trying to make a Pie chart)

Do you have any suggestions?

Thanks

[edhans]

A couple of things that might help.

First, consider using SWITCH instead of multiple nested IF statements. So

METAS1 =
IF(
    [MTTR DIC]
        > VALUE( META_MTTR[MTTR MAX] );
    "INEFICIENTE";
    IF(
        [MTTR DIC]
            < VALUE( [MTTR PROM] );
        "EXCEDE";
        IF(
            AND(
                [MTTR DIC]
                    < VALUE( [MTTR MAX] );
                [MTTR DIC]
                    > VALUE( [MTTR PROM] )
            );
            "CUMPLE META";
            "n/d"
        )
    )
)

Becomes:

METAS1 =
SWITCH(
    TRUE(),
    [MTTR DIC] > VALUE( META_MTTR[MTTR MAX] ), "INEFICIENTE",
    [MTTR DIC] < VALUE( [MTTR PROM] ), "EXCEDE",
    [MTTR DIC] < VALUE( [MTTR MAX] )
        && [MTTR DIC] > VALUE( [MTTR PROM] ), "CUMPLE META",
    "n/d"
)

Note I haven't validated that measure because I didn't have data I could copy and paste into Power BI.

It doesn't work in a Pie Chart though because there are no values. You need values to make a visual work, and you have two columns of text.

Create this measure in your table:

Data Account = COUNTROWS(TableName)

Now drop that value in the pie chart. Use the METAS1 measure as your legend for the chart.

This will just tell you the number of times each label in METAS1 shows up. You could use any measure that evaluates to a numerical value as your Value amount fo the visual.

[Anonymous]

Hey @edhans 

Thanks!

I change the measure from "if" to "switch", and help a lot.

But, I can´t make the pie chart yet, although I created the "Data account" measure, I am trying to put METAS1 in the legend section but it still doesnt allow me to do it.

Do you have any idea?

[edhans]

I'd have to see your PBIX file or more info. It works fine for me.

You can actually drag your METAS1 field to Values and tell Power BI to do a count, but that can cause problems later if you make any changes to how METAS1 works. Always best to do an explicit measure, even for something as simple as a row count.

Post a sample PBIX file if this isn't working for you. Use OneDrive or something similar to share.

[edhans · Accepted solution]

Ok, @Anonymous  I got your file. I did a little digging and now it makes sense to me. Measures return a single value, which depends on the filter context. You cannot use that in a legend. You have two options that I see:

1. Create the field values in Power Query. That will be difficult because of the way the data is stored in other tables and will require joins.
2. Create a calculated column. Your SWITCH measure will actually work there, but you'll need to test. You can drop that into a legend field of a Pie Chart.

Then create a measure you want in your values part of the chart. 

Test Value = COUNTROWS(META_MTTR) for example will count the number of records for each META and show the relevant percentages in the chart.

As a slight aside, if you will convert your [MTTR promedio (horas)] and [MTTR máx (horas)] to numbers in Power Query (the Tipo cambiado step) click the little ABC in the upper left corner of that column and change it to a number, then you can dispense with all of the VALUE() functions in your model.

Does that help? The issue is you really need to have most/all of your fields you want to slice and dice on coming in as fields from Power Query, and use measure to be the values. There are a lot of places measures cannot work. Slicers, filters, and as we've discovered, many parts of a lot of visuals, because measures return 1 value at calc. You see many in a table, but in reality, it is one value. It just recalculates the measure for that row of the table based on the filter context. It isn't returning a column of 4/5 values you can use as a true column.