Extracting from a column based on another column

[Anonymous]

Hello!

My table is the following:

Product ID	ICD Number
1	I45
1	I46
1
2	I45
3	I46
4

My desired output should be the following.

4

So the second table should only contain the product numbers that have no ICD number assocated with it which is Product ID 4 in this case.

[v-eqin-msft]

Hi @Anonymous ,

According to my understanding, you want to display the Product ID whose ICD Number is blank, right?

You could use the following formula:

ICDFlag =
IF ( ISBLANK ( SELECTEDVALUE ( 'Table'[ICD] ) ), 0, 1 )

sumFlag =
IF (
    CALCULATE (
        SUMX ( 'Table', [ICDFlag] ),
        ALLEXCEPT ( 'Table', 'Table'[Product ID] )
    ) = 0,
    1,
    0
)

My visualization looks like this:

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[amitchandak]

@Anonymous , try like

selectcolumns(filter(summarize(table, table[Product ID], "ICD",calculate(count(Table[Product ID]),not(isblank(Table[ICD Number])))
, "ICD1",calculate(count(Table[Product ID]),isblank(Table[ICD Number]))), isblank([ICD]) && not(isblank([ICD1]))) ,"Product ID",[Product ID])

[ryan_mayu]

@Anonymous

you can try to create a new table

Table 2 = 
var tbl=SUMMARIZE('Table','Table'[productID],"COUNT",CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[ICD Number]<>"")))
return SELECTCOLUMNS(FILTER(tbl,ISBLANK([COUNT])),"productID",'Table'[productID])