Forum Discussion
Solved
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 ...
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 )
jdbuchanan71
Super User
Super UserGive 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.
