Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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: 

 

Capture1.JPG

Result column will be like:

 

Capture2.JPG

Thanks

Jyaul

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Samarth_18
Community Champion
Community Champion

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

Samarth_18_0-1644830822739.png

 

Thanks,

Samarth

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

ryan_mayu
Super User
Super User

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!