Forum Discussion
Use of DistinctCount with multiple AND criteria
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.
Thanks in advance for the help...
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 )
4 Replies
- jdbuchanan71
Super User
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 )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.
- 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" ) - 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)
