locked
DAX Text String Comparison RRS feed

  • 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 PM
    Answerer
  • 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 PM
    Answerer
  • 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 PM
    Answerer
  • 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 PM
    Answerer