Calculate column

[Anonymous]

Dear Friends

i have a table(column: PoNo. & Doc No.) of thousands of rows, PO column have repeated value with corresponding DocNo as shown.

i need to create one more  column "Result-Missing" that have results like:

                                         if all rows of individual each PO have DocNo then result is "No Missing" 

                                         elseif all rows of each PO have all blank DocNo then result is "Fully Missing"

                                       else all rows of each PO have some blank then result is "Partial Missing"

table like: 

 

Result column will be like:

 

Thanks

Jyaul

[Samarth_18]

Hi @Anonymous ,

You can create a column with below code:-

Column =
VAR po_count =
    CALCULATE (
        COUNT ( 'Table (2)'[PO No] ),
        FILTER (
            ALL ( 'Table (2)' ),
            'Table (2)'[PO No] = EARLIER ( 'Table (2)'[PO No] )
        )
    )
VAR doc_count =
    CALCULATE (
        COUNT ( 'Table (2)'[Doc no] ),
        FILTER (
            ALL ( 'Table (2)' ),
            'Table (2)'[PO No] = EARLIER ( 'Table (2)'[PO No] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        doc_count = 0, "Fully Missing",
        po_count = doc_count, "No Missing",
        doc_count < po_count, "Partial Missing"
    )

Output:-

Thanks,

Samarth

[ryan_mayu · Accepted solution]

@Anonymous 

pls try this

Column = 
VAR check1=maxx(FILTER('Table','Table'[PO no.]=EARLIER('Table'[PO no.])&&'Table'[Doc no.]=""),'Table'[PO no.])
VAR check2=maxx(FILTER('Table','Table'[PO no.]=EARLIER('Table'[PO no.])&&'Table'[Doc no.]<>""),'Table'[PO no.])
return if(check1<>""&&check2="","Fully Missing",if(check1=""&&check2<>"","No missing","Partial missing"))