Hi all, Let's assume I have the following table Column1Column21B1A2A2B3A4B5B6C6B7C I need to create a c custom column with the priority of A and if NOT, find B, if NOT show null.Examplle:Column1Column2CustomColumn1BA1AA2AA2BA3AA4BB5BB6CB6BB7Cnull Highly appreciate your support!

Hi arman_tale - check the below calculated column and change the table name as per your source. Custom1 = VAR CurrentValue = 'Vlook'[Column2] VAR PriorityA = CALCULATE(MAX('Vlook'[Column2]), 'Vlook'[Column1] = EARLIER('Vlook'[Column1]) && 'Vlook'[Column2] IN {"A", "B"}) VAR PriorityC = CALCULATE(MAX('Vlook'[Column2]), 'Vlook'[Column1] = EARLIER('Vlook'[Column1]) && 'Vlook'[Column2] IN {"C", "D"}) VAR PriorityE = CALCULATE(MAX('Vlook'[Column2]), 'Vlook'[Column1] = EARLIER('Vlook'[Column1]) && 'Vlook'[Column2] IN {"E", "F"}) RETURN IF(CurrentValue IN {"A", "B"}, PriorityA, IF(CurrentValue IN {"C", "D"}, PriorityC, IF(CurrentValue IN {"E", "F"}, PriorityE, BLANK()))) Hope it works

IF combination with LOOKUP | Microsoft Fabric Community

9 Replies

SamWiseOwl
Super User
2 years ago
Hi arman_tale
You could do this in the front end with a Calculated Column :
Custom Column =
var CurrentGroup = [Column1] --Capture group
var SameGroup = --Filter to same group
SELECTCOLUMNS(
Filter('Table', [Column1] = CurrentGroup)
,[Column2]) --return single column
RETURN
SWITCH(
TRUE()
,"A" IN SameGroup
,"A"
,"B" IN SameGroup
,"B"
,"null"
)
rajendraongole1
Super User
2 years ago
Hi arman_tale -you can do group by Column1, and for the Column2 column, create an "All Rows" operation (this will create a table for each Column1).

add one new column (Custom column) with below if condition as :

if Table.Contains([AllRow], [Column2 = "A"]) then "A"
else if Table.Contains([AllRow], [Column2 = "B"]) then "B"
else null

Expand Allrows column2

output:

Hope it helps
arman_tale
New Member
2 years ago
rajendraongole1 SamWiseOwl
That works perfect, thank you. But I actually realized the problem I am facing is a bit deeper than that:
Let's say Column1 is independant variable, but I have dependant variables in groups of 2 (A and B), (C and D), (E and F). A has priority to B, C has priority to D and E has priority to F, and I need to show all the independant Column1 variables with the highest available priority variable in each group
I need my customcolumn to look like this

Column1 Column2 Custom
1 B A
1 A A
1 C C
1 D C
2 A A
2 D C
2 C C
3 F F
4 E E
4 F E
4 B B
5 A A
5 B A
5 C C
5 D C
5 E E
5 F E

Appreciate your feedback and support!
- rajendraongole1
  Super User
  2 years ago
  Hi arman_tale - Create a new custom column in power query editor as below
  
  Custom =
  
  if [Column2] = "A" or [Column2] = "B" then "A"
  else if [Column2] = "C" or [Column2] = "D" then "C"
  else if [Column2] = "E" or [Column2] = "F" then "E"
  else null
  
  output:
  - arman_tale
    New Member
    2 years ago
    rajendraongole1
    This doesn't preceisely work the way I required. Look at the issue in the screenshot:
    
    Any approach to overcome this issue?
ryan_mayu
Super User
2 years ago
arman_tale

you can try this

Column =

VAR _p=CALCULATE(min('Table'[Column2]),ALLEXCEPT('Table','Table'[Column1]))

return if (_p="A", "A",if(_p="B","B",blank()))