select value from one column filter with another column

[sohailstsindia]

TABLE NAME
srl no	srl ref.	desc
1	1	a
2	1	b
3	1	c
4	4	a
5	4	b
6	4	c

Filter value from srlno search in srl ref.(if srl = srl ref. and desc = "b" then srl no in new col1),( if srl = srl ref. and desc = "c" then srl no in new col2)

TABLE NAME
srl no	srl ref.	desc	new col1	new col2
1	1	a	2	3
4	4	a	5	6

[v-xuding-msft · Accepted solution]

Hi @sohailstsindia ,

Please try this:

b =
CALCULATE (
    MAX ( 'Table'[srl no] ),
    FILTER ( ALLEXCEPT ( 'Table', 'Table'[srl ref.] ), 'Table'[desc] = "b" )
)

c =
CALCULATE (
    MAX ( 'Table'[srl no] ),
    FILTER ( ALLEXCEPT ( 'Table', 'Table'[srl ref.] ), 'Table'[desc] = "c" )
)

[sohailstsindia]

Thank @v-xuding-msft 

[manikumar34]

@sohailstsindia , Create relationship srl no and use RELATED function with IF .

https://www.sqlbi.com/daxpuzzle/related-in-a-query/

Regards, 

Manikumar

[sohailstsindia]

thanks @Manikumar can you please write DAX.