cancel
Showing results for
Did you mean:
Resolver I

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

Hi all,

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

1 ACCEPTED SOLUTION
Solution Sage

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.
4 REPLIES 4
Solution Sage

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.
Resolver I

Hi JPPV20,

this works perfect thank you.

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

Thomas

Super User

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

Super User

@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 mor conditions as per need

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors