Creating a measure based on one physical columns and one measure???

[thomasreick]

Hi all,

please recommend on the following...

I need to calculate a measure out of two columns within the same table.

One is a physical column, one is result of count-aggregation.

One column should be COUNT(Index)), the other is a disjunctive condition set by PowerQuery in field [tmp].

Values for [tmp] are either 5 or 0.

I do not get the point on how to set IF-Statement....

IF (AND([tmp]=5), COUNT([Index])>=5,5,

IF (AND([tmp]=0), COUNT([Index])>=5,4,

IF (AND([tmp]=0), COUNT([Index])=4,3,

IF (AND([tmp]=0), COUNT([Index])=3,2,

IF (AND([tmp]=0), COUNT([Index])=2,2,

IF (AND([tmp]=0), COUNT([Index])=1,1,0))))

All hints welcome.

thx&brgds

Thomas

[jppv20 · Accepted solution]

Hi @thomasreick ,

Try this measure:

Measure =

IF(AND(MAX('Table'[TMP])=5,COUNT([Index])>=5),5,

IF (AND(MAX('Table'[TMP])=0, COUNT([Index])>=5),4,

IF (AND(MAX('Table'[TMP])=0, COUNT([Index])=4),3,

IF (AND(MAX('Table'[TMP])=0, COUNT([Index])=3),2,

IF (AND(MAX('Table'[TMP])=0, COUNT([Index])=2),2,

IF (AND(MAX('Table'[TMP])=0, COUNT([Index])=1),1,0))))))

If I answered your question, please mark it as a solution to help other members find it more quickly.

[thomasreick]

Hi JPPV20,

this works perfect thank you.

I think I got lost somewhere in between the brackets...

Thomas

[HotChilli]

To debug an IF statement, reduce it to it's simplest form, make that work and then build it up.

So, for starters, make the first line work. Hint : AND takes 2 parameters

[amitchandak]

@thomasreick , It need to be a measure like

Switch(True(),
(AND(Max(Table[tmp])=5), COUNT([Index]) ) >=5,5,
(AND(Max(Table[tmp])=0), COUNT([Index]) ) >=5,4
// Add others
)

Add mor conditions as per need