Way to display a "word" instead of a "number".

[jmuniz]

Hi Everyone:

I am looking for a way that, instead of having the values in number, I want a word. For example: If the result is between 2.0 and 2.99 it should say "Emerging". Any ideas?

I tried to do a table and make a relationship but I don't think it doesn't work since Power BI is calculating those results.

[jdbuchanan71]

Hello @jmuniz 

You can use a measure like this to display the word you want.

Measure = 
VAR AvgResult = CALCULATE ( AVERAGE ( YourTable[Result] ) )
RETURN
SWITCH (
    TRUE(),
    AvgResult > 4, "Master",
    AvgResult > 3, "Expert",
    AvgResult > 2, "Emerging",
    "Student"
)

 This measure will calc dynamically so any slicers or filters you apply to the visual will be respected.

[jmuniz]

Hi @jdbuchanan71:

So, this can be used for the final aggregation? The final aggregation probably will be on decimals and can look like this:

1 to 1.99 is Novice

2 to 2.99 is Emerging

3 to 3.99 is Effective

4 to 4.99 is Advance

5 and up is Expert

[jdbuchanan71 · Accepted solution]

You would do it like this.  No need to put in the range, the SWITCH ( TRUE() will find the first match starting from the top of the list.

Measure = 
VAR AvgResult = CALCULATE ( AVERAGE ( YourTable[Result] ) )
RETURN
SWITCH (
    TRUE(),
    AvgResult >= 5, "Expert",
    AvgResult >= 4, "Advanced",
    AvgResult >= 3, "Effective",
    AvgResult >= 2, "Emerging",
    AvgResult >= 1, "Novice",
    "Other"
)

So, if the average is 3.82 it would do the following tests:
3.82 >= 5 = FALSE next

3.82 >= 4 = FALSE next

3.82 >= 3 = TRUE "Effective"

Once it returns a result it stops checking.  The final check applies if there is anyting < 1 or blank it would apply "Other"

[jmuniz]

@jdbuchanan71  You're the best, it worked! 

[Mond]

Hi,

As @Anonymous says, create a conditional column in the model with "if condition"

For ex: =if( sum([mastery level]) >=  2.0 and sum([mastery level]) <=2.99, "Emerging", if (sum([mastery level]) >=  3.0 and sum([mastery level]) <=3.99, "Non-Emerging", "NA"))

[Anonymous]

@Mond the table above in the original post is an aggregation of data, so i believe it is best to have a measure which evaluate the final aggregation.

I simply didn't know that the requirement is not for the underlying table, however, the aggregation of the data.

[jdbuchanan71]

Hello @jmuniz 

Is the [Mastery Level] from a measure or a column in a table?

[jmuniz]

Hi @jdbuchanan71 :

The Mastery Level comes from a table that is imported from excel. 

[Anonymous]

Why not to add a conditional column to your query?

[jmuniz]

Hi @Anonymous :

I have done it before, however (correct if I'm wrong since I'm a new user using power bi), the system will not calculate the last column showing here since is a text and I want it does the average to display my original table showed at the beginning of the thread.