Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate column

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" ...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    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"))

Samarth_18's avatar
Samarth_18
Community Champion
4 years ago

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