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

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

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    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