cancel
Showing results for
Did you mean:
Regular Visitor

## DAX Create Calculated Field that Performs Calculations Based on Adjacent Field

Result TableThis my table and respective fields. I would like to create a column called "Availability" which will look at the "STAT" field and determine what operation to perform. If STAT = I0099 or E0002 then blank otherwise take the item in the DIF field and divide it over the figures in the DIF field for STAT I0099 and E0002 (2824-98). For example on the 3rd row the field should be (52/(2824-98)

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: DAX Create Calculated Field that Performs Calculations Based on Adjacent Field

You want this done per OBNJR? I will assume so. Try this for your new  calculated column, where Table1 is the table you show:

```Availability =
IF (
NOT Table1[STAT] IN { "I0099", "E0002" },
DIVIDE (
Table1[DIF],
CALCULATE (
DISTINCT ( Table1[DIF] ),
Table1[STAT] = "I0099",
ALLEXCEPT ( Table1, Table1[OBJNR] )
)
- CALCULATE (
DISTINCT ( Table1[DIF] ),
Table1[STAT] = "E0002",
ALLEXCEPT ( Table1, Table1[OBJNR] )
)
)
)```

On a general note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

If you had provided the data in text format, I could have copied easily and run a pretty quick test to make sure the code works as expected.

Cheers

Code formatted with

2 REPLIES 2
Super User

## Re: DAX Create Calculated Field that Performs Calculations Based on Adjacent Field

You want this done per OBNJR? I will assume so. Try this for your new  calculated column, where Table1 is the table you show:

```Availability =
IF (
NOT Table1[STAT] IN { "I0099", "E0002" },
DIVIDE (
Table1[DIF],
CALCULATE (
DISTINCT ( Table1[DIF] ),
Table1[STAT] = "I0099",
ALLEXCEPT ( Table1, Table1[OBJNR] )
)
- CALCULATE (
DISTINCT ( Table1[DIF] ),
Table1[STAT] = "E0002",
ALLEXCEPT ( Table1, Table1[OBJNR] )
)
)
)```

On a general note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

If you had provided the data in text format, I could have copied easily and run a pretty quick test to make sure the code works as expected.

Cheers

Code formatted with

Regular Visitor

## Re: DAX Create Calculated Field that Performs Calculations Based on Adjacent Field

Hi @AlB this works perfectly, thank you so much. Sorry about not including script. Will make sure to do next time.  Perfection, see below.

CountOBJNRYearModelSTATTXT30DIFEquipmentAvailability

 1 IE000000000010003137 2011 Orion Hybrid 40' I0099 Available 2824 10003137 2 IE000000000010003137 2011 Orion Hybrid 40' E0002 Awaiting Acceptance 98 10003137 4 IE000000000010003137 2011 Orion Hybrid 40' E0013 Non Operational Parts 52 10003137 2% 4 IE000000000010003137 2011 Orion Hybrid 40' E0019 Operational Backorder 405 10003137 15% 7 IE000000000010003137 2011 Orion Hybrid 40' E0004 Operational 2269 10003137 83%