How to calculate count of repeating Sales Orders with conditions?

[PBIUWO]

Hi, 

I have a table of Sales Order data. Order Number, Item Code, and a Flag.

It contains a flag with 0 or 1 value.

OrderNumber	Item Code	Flag
123	ABC	1
123	CDE	0
456	CDE	0
456	GHJ	0
789	GHJ	0
111	ABC	1

The Order Numbers can be repeated, but I want to distinct count the Order Numbers with the following conditions.

1. How many Orders Numbers contain both the 0 & 1 flag. (Answer from the Example is 1)  

2. How many Orders contain "0" Flag, (Answer from the Example is 3)

3, How many Orders contain "1" Flag. (Answer from the example is 2)

4. How many Orders contain ONLY the "1" Flag (Answer from the example is 1)

5. How many Orders contain ONLY the "0" Flag (Answer from the example is 2) 

It'll be nice to also have a column beside the flag, where it says "Mixed" (has both 0 & 1), "1" (Has ONLY 1), "0" (Has ONLY 0) for the row.  

I'm able to create a column where I can show if an order contains a 1, but I realize that there can be Order Numbers where it only contains the 1 and not a mix (1 and 0) 

I'm able to think that a "virtual table" can solve this but am running into the problem of populating the value with the filters.

Thank you in Advance! 

[jdbuchanan71 · Accepted solution]

@PBIUWO 
I have attached a sample .pbix with the measures for each of the results and the calculated column.

The measures are just different compares for the counts of 0's and 1's

0 & 1 = 
VAR _0 = CALCULATETABLE ( DISTINCT ( 'Table'[OrderNumber] ), 'Table'[Flag] = 0 )
VAR _1 = CALCULATETABLE ( DISTINCT ( 'Table'[OrderNumber] ), 'Table'[Flag] = 1 )
RETURN 
    COUNTROWS( INTERSECT ( _0 , _1 ) )

0 = 
VAR _0 = CALCULATETABLE ( DISTINCT ( 'Table'[OrderNumber] ), 'Table'[Flag] = 0 )
VAR _1 = CALCULATETABLE ( DISTINCT ( 'Table'[OrderNumber] ), 'Table'[Flag] = 1 )
RETURN 
    COUNTROWS( _0 )

1 Only = 
VAR _0 = CALCULATETABLE ( DISTINCT ( 'Table'[OrderNumber] ), 'Table'[Flag] = 0 )
VAR _1 = CALCULATETABLE ( DISTINCT ( 'Table'[OrderNumber] ), 'Table'[Flag] = 1 )
RETURN 
    COUNTROWS( EXCEPT ( _1, _0 ) )

The calculated column is like this.

Check = 
VAR _0 = COUNTROWS ( CALCULATETABLE ( DISTINCT ( 'Table'[OrderNumber] ), 'Table'[Flag] = 0, ALLEXCEPT ( 'Table', 'Table'[OrderNumber] ) ) )
VAR _1 = COUNTROWS ( CALCULATETABLE ( DISTINCT ( 'Table'[OrderNumber] ), 'Table'[Flag] = 1, ALLEXCEPT ( 'Table', 'Table'[OrderNumber] ) ) )
RETURN 
    SWITCH (
        TRUE(),
        _0 > 0 && _1 > 0, "Has Both",
        _0 > 0 && _1 = 0, "0 Only",
        _0 = 0 && _1 > 0, "1 Only"
    )

[PBIUWO]

Thank you! 

I totally forgot about the Calculatetable(). 

[amitchandak]

@PBIUWO , try measure like

contain both the 0 & 1 =
countx(filter(summarize(Table, Table[OrderNumber], "_1", calculate(distinctcount(Table[Flag]),filter(Table, Table[Flag] in {0,1}))) , [_1]=2),[OrderNumber])

contain only 0 =
countx(filter(summarize(Table, Table[OrderNumber], "_1", calculate(distinctcount(Table[Flag]),filter(Table, Table[Flag] in {0})) , "_2",distinctcount(Table[Flag]) ) , [_1]=[_2]),[OrderNumber])

contain only 1 =
countx(filter(summarize(Table, Table[OrderNumber], "_1", calculate(distinctcount(Table[Flag]),filter(Table, Table[Flag] in {1})) , "_2",distinctcount(Table[Flag]) ) , [_1]=[_2]),[OrderNumber])

contain 0 =calculate(distinctcount(Table[Flag]),filter(Table, Table[Flag] in {0}))

contain 1 =calculate(distinctcount(Table[Flag]),filter(Table, Table[Flag] in {1}))