cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Help with calculated column

I got an invoice table listing all its items, I need a calculated column to identify whether the invoice contains only a SINGLE or MIXED category based on the category column. Below is the sample and the desired output for the status column.

 

Thanks for all the help.

 

INVOICE NUMBERITEMCATEGORYSTATUS
48456558TEABEVERAGESINGLE
48456558SODABEVERAGESINGLE
48456558SODABEVERAGESINGLE
48456558JUICEBEVERAGESINGLE
48459124SODABEVERAGEMIXED
48459124JUICEBEVERAGEMIXED
48459124CANDYCONFECTIONARYMIXED
48459124CHEESEDAIRYMIXED
48459124SODABEVERAGEMIXED
48461354CHEESEDAIRYSINGLE
48461354BUTTERDAIRYSINGLE
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Help with calculated column

HI @jrpoli2000

 

Try this

 

Status =
IF (
    CALCULATE (
        DISTINCTCOUNT ( Table1[CATEGORY] ),
        ALLEXCEPT ( Table1, Table1[INVOICE NUMBER] )
    )
        > 1,
    "MIXED",
    "SINGLE"
)
Regards,
Zubair


View solution in original post

2 REPLIES 2
Highlighted
Super User III
Super User III

Re: Help with calculated column

HI @jrpoli2000

 

Try this

 

Status =
IF (
    CALCULATE (
        DISTINCTCOUNT ( Table1[CATEGORY] ),
        ALLEXCEPT ( Table1, Table1[INVOICE NUMBER] )
    )
        > 1,
    "MIXED",
    "SINGLE"
)
Regards,
Zubair


View solution in original post

Highlighted
Frequent Visitor

Re: Help with calculated column

Thanks again!!!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors