cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
erikah06 Regular Visitor
Regular Visitor

DAX Create Calculated Field that Performs Calculations Based on Adjacent Field

Calculated Field Question.PNGResult 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
Super User

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

Hi  @erikah06

 

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   www.daxformatter.com

2 REPLIES 2
Super User
Super User

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

Hi  @erikah06

 

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   www.daxformatter.com

erikah06 Regular Visitor
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. Smiley Happy 

 

CountOBJNRYearModelSTATTXT30DIFEquipmentAvailability

1IE0000000000100031372011Orion Hybrid 40'I0099Available282410003137 
2IE0000000000100031372011Orion Hybrid 40'E0002Awaiting Acceptance9810003137 
4IE0000000000100031372011Orion Hybrid 40'E0013Non Operational Parts52100031372%
4IE0000000000100031372011Orion Hybrid 40'E0019Operational Backorder4051000313715%
7IE0000000000100031372011Orion Hybrid 40'E0004Operational22691000313783%