CODEWITHSUNDEEP
powerbidaxdata-analysispowerbi-desktopcalculated-columns
Sebastián Frades
Sebastián Frades

Reputation: 25

Create a column based on multiple columns for certain rows Power BI

I have the next table in Power BI

Ubicación.Name Fecha_entrega__c Sector_entrante__c
PAD FP.c-1050 5/31/2021 Perforación
PAD LAnch.x-2(h) 4/30/2022 Terminación
PAD LAnch.x-2(h) 2/28/2022 Perforación
PAD LAnch.x-2(h) 7/13/2022 Well Testing
PAD de Pozos 1003 4/23/2022 Terminación
PAD de Pozos 1003 8/11/2022 Perforación

I would like to create a column based on the next logic

For a certain group in "Ubicación.Name", for example, "PAD LAnch.x-2(h)", there are three rows of this kind, I want to check whether the newest of these rows, based on "Fecha_entrega__c" column, has in "Sector_entrante__c" column the string "Well Testing". In this case we can see in row 4 which has the newest date of rows 2,3 and 4 (all of them part of "PAD LAnch.x-2(h)") that in column "Sector_entrante__c" it says "Well Testing", so I want a column that gives the number 1 for rows 2,3 and 4. If it didn't have "Well Testing" I would like to give the value 0 for rows 2,3 and 4.

Ubicación.Name Fecha_entrega__c Sector_entrante__c Column
PAD FP.c-1050 5/31/2021 Perforación 0
PAD LAnch.x-2(h) 4/30/2022 Terminación 1
PAD LAnch.x-2(h) 2/28/2022 Perforación 1
PAD LAnch.x-2(h) 7/13/2022 Well Testing 1
PAD de Pozos 1003 4/23/2022 Terminación 0
PAD de Pozos 1003 8/11/2022 Perforación 0

Thanks.

The only thing I have been able to do is to use Rank and Filter to identify with numbers which are the newest and oldest of these group

RANK Column =
  RANKX(
    FILTER(
      'Form  NQN PAD Handover (3)',
      'Form  NQN PAD Handover (3)'[Ubicación.Name] = EARLIER('Form  NQN PAD Handover (3)'[Ubicación.Name])
    ),
    'Form  NQN PAD Handover (3)'[Fecha_entrega__c],
    ,DESC
  )

But I really have no idea how to apply logic to certain groups within columns.

Upvotes: 1

Views: 72

Answers (1)

davidebacci
davidebacci

Reputation: 30219

enter image description here

Column = 
VAR x = CALCULATE(MAX('Table'[Fecha_entrega__c]), ALLEXCEPT('Table', 'Table'[Ubicación.Name]))
VAR y = CALCULATE(MAX('Table'[Sector_entrante__c]), 'Table'[Fecha_entrega__c] = x, ALLEXCEPT('Table', 'Table'[Ubicación.Name]))

RETURN IF(y = "Well Testing", 1,0)

Upvotes: 1

Related Questions