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

Post Prodigy

## Measure with criteria

Hello

I want to create a measure and calculated column (later I need to make groups of these) where

If value from the same line same table of Field1 Equals value from the same line same table of Field2 then
Divide (FieldX/FieldY) Else 0

Would it be like CALCULATE (IF (Table1[Field1] = Table1[Field2], DIVIDE (Table1[FieldX], Table1[FieldY]), 0 ) ?

The point is to exclude calculation in a column and measure when there is no meet criteria Table1[Field1] = Table1[Field2]

1 ACCEPTED SOLUTION
Community Support

Hi  @Pbiuserr ,

I created some data:

Table1：

Table2:

Here are the steps you can follow：

1. Add Index to Table1 and Table2 in Power Query.

In Power query. Add Column – Index Column – From 1.

2. Create measure.

``````Measure_Table2 =
MAXX(FILTER(ALL(Table1),'Table1'[Index]=MAX('Table2'[Index])),[Field1])``````
``````Measure with criteria =
IF(
MAX('Table2'[Field1])=[Measure_Table2],
DIVIDE(MAX('Table1'[FieldX]),MAX('Table1'[FieldY]))
,0)``````

3. Result:

Measure with criteria.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

3 REPLIES 3
Community Support

Hi  @Pbiuserr ,

I created some data:

Table1：

Table2:

Here are the steps you can follow：

1. Add Index to Table1 and Table2 in Power Query.

In Power query. Add Column – Index Column – From 1.

2. Create measure.

``````Measure_Table2 =
MAXX(FILTER(ALL(Table1),'Table1'[Index]=MAX('Table2'[Index])),[Field1])``````
``````Measure with criteria =
IF(
MAX('Table2'[Field1])=[Measure_Table2],
DIVIDE(MAX('Table1'[FieldX]),MAX('Table1'[FieldY]))
,0)``````

3. Result:

Measure with criteria.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Super User

Hi Pbiuserr

For create the calculated column you don't need the "CALCULATE".

Try:

IF (

Table1[Field1] = Table1[Field2],

DIVIDE (Table1[FieldX], Table1[FieldY]),

0

)

After you can creat the measure for this new column.

Best Regards
BC

Best regards

Bruno Costa | Super User

Take a look at the blog: PBI Portugal

Post Prodigy

Hi

Thank you for your reply! For the measure, if the criteria is made, id like to have sum of FieldX / sum of FieldY