Use of DistinctCount with multiple AND criteria

[faustoalvarez]

Hi experts,

I have the following table

Im using  DISTINCTCOUNT get the total number of GUESTCHECKNUMBER checks that contain a certain product, it works fine when I try to get Checks with 1 MENUITEMNAMES product or Checks that have 1 or 2 or 3 MENUITEMNAMES.  However, what i really want to do is count Checks that have 1 MENUITEMNAMES "AND" another MENUITEMNAMES product, and it seemed that the && its not working at all.

My question here, how can I Count the GUESTCHECKNUMBER (checks) that has more than 1 product (MENUITEMNAMES) that I select as the example below.

PAPAS_ADEREZOS =

    CALCULATE(

     DISTINCTCOUNT(CHEQUE_SEM[GUESTCHECKNUMBER]),

      FILTER (CHEQUE_SEM,LEFT(CHEQUE_SEM[MENUITEMNAME],14) ="Papas Francesa" && LEFT(CHEQUE_SEM[MENUITEMNAME],12)="Papas Crispy" && LEFT(CHEQUE_SEM[MENUITEMNAME],10)="Papas Gajo"),

      FILTER (CHEQUE_SEM,CHEQUE_SEM[PARENTID] = 0)

    )

Thanks in advance for the help...

[Ashish_Mathur]

Hi,

Try this measure

PAPAS_ADEREZOS = CALCULATE(DISTINCTCOUNT(CHEQUE_SEM[GUESTCHECKNUMBER]),FILTER (CHEQUE_SEM,LEFT(CHEQUE_SEM[MENUITEMNAME],14) ="Papas Francesa" ||LEFT(CHEQUE_SEM[MENUITEMNAME],12)="Papas Crispy"||LEFT(CHEQUE_SEM[MENUITEMNAME],10)="Papas Gajo"),CHEQUE_SEM[PARENTID] = 0)

[jdbuchanan71]

You could do that like this.

OR count = 
CALCULATE(
    DISTINCTCOUNT(CHEQUE_SEM[GUESTCHECKNUMBER]), 
        LEFT ( 'CHEQUE_SEM'[MENUITEMNAME], 4 ) = "Blue"  ||
        LEFT ( 'CHEQUE_SEM'[MENUITEMNAME], 5 ) = "Black"  ||
        LEFT ( 'CHEQUE_SEM'[MENUITEMNAME], 6 ) = "Orange"     
)

[jdbuchanan71 · Accepted solution]

@faustoalvarez 

Give something like this a try.  It works in my testing.

Count = 
VAR _Blue = CALCULATETABLE ( VALUES ( 'Table'[GUESTCHECKNUMBER] ), LEFT ( 'Table'[MENUITEMNAME], 4 ) = "Blue" )
VAR _Black = CALCULATETABLE ( VALUES ( 'Table'[GUESTCHECKNUMBER] ), LEFT ( 'Table'[MENUITEMNAME], 5 ) = "Black" )
VAR _Orange = CALCULATETABLE ( VALUES ( 'Table'[GUESTCHECKNUMBER] ), LEFT ( 'Table'[MENUITEMNAME], 6 ) = "Orange" )
RETURN 
CALCULATE ( DISTINCTCOUNT('Table'[GUESTCHECKNUMBER]), _Black, _Blue, _Orange )

[faustoalvarez]

Thanks for the help... seemed to be working fine.

I have a new question (based on your example):  How can I do the following:

- DistinctCount the rows that comply with having either (Blue or Black) and Orange?

Meaning that i can use OR and AND in the same measurement.