cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.