Answered by:
DAX Text String Comparison
Question

Hi guys,
I'm trying to come up with a formula that will calculate the number of lines where two conditions are true.
First, SLA must be either breached or achieved
And the second condition must be that the "country" and SLO group must be the same (these two values are located in different tables.
So far I have only accomplished the first....
=CALCULATE(DISTINCTCOUNT([ID]),Data![SLA Result]="Breached")
I have tried adding FIND, EXACT or USERELATIONSHIP to the formula to no avail.... I keep running into the same error
"The value for 'SLO Group' cannot be determined. Either 'SLO Group' doesn't exist, or there is no current row for a column named 'SLO Group'."
Any ideas?
Thanks a lot.
Country SLO Group SLA Achieved SLA Breached AR_POMDSKT # 1733 382 AR_POMDSKT AR_POMDSKT 1572 286 AR_POMDSKT MX_POMSD 26 BR_POMDSKT # 1152 738 BR_POMDSKT BR_POMDSKT 1373 258 BR_POMDSKT BR_POMSD 179 18 BR_POMSD # 640 329 BR_POMSD BR_POMDSKT 67 2 BR_POMSD BR_POMSD 779 240 BR_POMSD MX_POMSD 6 BR_POMSD PE_POMDSKT 1 BR_POMSD US_POMSD 3 CA_POMDSKT # 2971 137 CA_POMDSKT CA_POMDSKT 2798 31 CA_POMDSKT US_POMDSKT 6 CA_POMDSKT US_POMSD 273 19 CO_POMDSKT # 1728 62 CO_POMDSKT BR_POMSD 1 CO_POMDSKT CO_POMDSKT 1538 47 CO_POMDSKT MX_POMSD 42 CO_POMDSKT PE_POMDSKT 2 CO_POMDSKT US_POMSD 1 CO_POMDSKT VE_POMDSKT 3 CR_POMDSKT # 94 18 CR_POMDSKT CR_POMDSKT 87 18 CR_POMDSKT MX_POMSD 3 CR_POMDSKT SV_POMDSKT 1 DO_POMDSKT # 44 16 DO_POMDSKT DO_POMDSKT 46 8 DO_POMDSKT MX_POMSD 1 DO_POMDSKT SV_POMDSKT 1 EC_POMDSKT # 262 7 EC_POMDSKT CO_POMDSKT 7 EC_POMDSKT EC_POMDSKT 225 15 EC_POMDSKT MX_POMSD 13 EC_POMDSKT PE_POMDSKT 1 GT_POMDSKT # 217 18 GT_POMDSKT DO_POMDSKT 2 GT_POMDSKT GT_POMDSKT 233 GT_POMDSKT MX_POMSD 7 MX_POMDSKT # 1609 72 MX_POMDSKT MX_POMDSKT 1442 11 MX_POMDSKT MX_POMSD 11 MX_POMSD # 425 54 MX_POMSD AR_POMDSKT 7 2 MX_POMSD BR_POMSD 9 4 MX_POMSD CO_POMDSKT 5 MX_POMSD GT_POMDSKT 1 MX_POMSD MX_POMDSKT 2 MX_POMSD MX_POMSD 451 13 MX_POMSD PE_POMDSKT 1 MX_POMSD SV_POMDSKT 2 MX_POMSD US_POMDSKT 2 MX_POMSD US_POMSD 4
Monday, June 1, 2015 3:07 AM
Answers

Hi,
this is actually easier than you think:
=CALCULATE(DISTINCTCOUNT([ID]), Filter(Data!, Data![SLA Result]="Breached" && Data![Country]=Data![SLO Group]))
:)
Imke
 Proposed as answer by Michael Amadi Monday, June 1, 2015 3:30 PM
 Marked as answer by JCA1990 Monday, June 1, 2015 3:34 PM
Monday, June 1, 2015 3:09 PMAnswerer 
HI JCA1990,
If you're using the above DAX to define a Calculated Field (or Measure), then you'll need to introduce a row context for EXACT(Data[SLO Group],Data[Country]) to be evaluated. If this is the case then something like the following should work...
= CALCULATE( DISTINCTCOUNT(Data[ID]), Data[SLA Result] = "Breached", FILTER( Data, EXACT( Data[SLO Group], Data[Country] ) ) )
Regards,
Michael Amadi
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)
Website: http://www.nimblelearn.com, Twitter: @nimblelearn Marked as answer by JCA1990 Monday, June 1, 2015 3:35 PM
Monday, June 1, 2015 3:13 PM
All replies

Hi,
how are the tables which carry both "SLO Groups" related?
Imke
Monday, June 1, 2015 12:52 PMAnswerer 
Hi there!
I decided to simplify things and adjusted the data in order to have only one table that contains the country and SLO group in the same row
=CALCULATE(DISTINCTCOUNT([ID]),Data![SLA Result]="Breached")
Country SLO Group SLA Achieved SLA Breached AR_POMDSKTP # 1733 382 AR_POMDSKTP AR_POMDSKT 1572 286 AR_POMDSKTP MX_POMSD 26 But if I try
=CALCULATE(DISTINCTCOUNT([ID]),Data[SLA Result]="Breached", EXACT(Data[SLO Group],Data[Country])
OR
=IF(EXACT([Country],[SLO Group]),CALCULATE(DISTINCTCOUNT([ID]),Data[SLA Result]="Breached"),0)
I get the same result
ERROR  CALCULATION ABORTED: Calculation error in measure 'GITT'[SLA Breached]: The value for 'SLO Group' cannot be determined. Either 'SLO Group' doesn't exist, or there is no current row for a column named 'SLO Group'. I have no idea what I'm doing wrong.
Monday, June 1, 2015 2:48 PM 
Hi,
this is actually easier than you think:
=CALCULATE(DISTINCTCOUNT([ID]), Filter(Data!, Data![SLA Result]="Breached" && Data![Country]=Data![SLO Group]))
:)
Imke
 Proposed as answer by Michael Amadi Monday, June 1, 2015 3:30 PM
 Marked as answer by JCA1990 Monday, June 1, 2015 3:34 PM
Monday, June 1, 2015 3:09 PMAnswerer 
HI JCA1990,
If you're using the above DAX to define a Calculated Field (or Measure), then you'll need to introduce a row context for EXACT(Data[SLO Group],Data[Country]) to be evaluated. If this is the case then something like the following should work...
= CALCULATE( DISTINCTCOUNT(Data[ID]), Data[SLA Result] = "Breached", FILTER( Data, EXACT( Data[SLO Group], Data[Country] ) ) )
Regards,
Michael Amadi
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)
Website: http://www.nimblelearn.com, Twitter: @nimblelearn Marked as answer by JCA1990 Monday, June 1, 2015 3:35 PM
Monday, June 1, 2015 3:13 PM 
Yay! it works. What is && though? does it work as an equivalent of the AND formula?
Thanks for your help
Monday, June 1, 2015 3:35 PM 
Correct, so you could combine many more filter arguments that way.
Imke
Monday, June 1, 2015 3:42 PMAnswerer