Count different values and give a classification (LOD in power BI)

[joaoresende]

Hello,

I came from Tableau and in this i use LOD to classificate

I have this table below and I need to classify each person according to the values ​​obtained in column Classification. For example if the person has the 3 classifications "HIGHER" then he will be "Indicated", if he has up to two "INCREASE" and one "HIGHER" then he will be "INDICATED BUT X" and has other metrics. How can I do this?

[az38]

Hi @joaoresende 

try to create a column with technique like this

Column = 
var _higherCount = CALCULATE(COUNTROWS(Table), ALLEXCEPT(Table, Table[Person_id]), Table[Classification] = "Higher")
var _increaseCount = CALCULATE(COUNTROWS(Table), ALLEXCEPT(Table, Table[Person_id]), Table[Classification] = "Increase")

RETURN

SWITCH(TRUE(),
_higherCount = 3, "Indicated",
_increaseCount  <= 2 _higherCount = 1, "INDICATED BUT X",
"Other"
)

[joaoresende]

Hello, @az38 

Thank you for answer, i was with a problem when i try to use this column. I think is because to get the "classification" i was using the measure bellow :

Measure : 

Classification = 
var percentil = [meanPercentil]
return
LOOKUPVALUE('Classification'[Classification];'Classification'[Percentil];percentil)

ERROR : 

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[az38 · Accepted solution]

@joaoresende 

you also can create a measure

Column = 
var _Classification = SELECTEDVALUE(Table[Classification])
var _higherCount = CALCULATE(COUNTROWS(Table), ALLEXCEPT(Table, Table[Person_id]), Table[Classification] = _Classification )
var _increaseCount = CALCULATE(COUNTROWS(Table), ALLEXCEPT(Table, Table[Person_id]), Table[Classification] = _Classification )

RETURN

SWITCH(TRUE(),
_higherCount = 3, "Indicated",
_increaseCount  <= 2 _higherCount = 1, "INDICATED BUT X",
"Other"
)

[joaoresende]

Thank you, i will try this.

The problem that i have with measure is to use on X-asis, i can't see in a graphic.

I send you a invite on Linked-In, if u have some time

[az38]

@joaoresende 

so,

you nave to re-design your data model as Measure can not be used both in Column expression and as X-axis