locked
Power BI Switch statement help with using dates split in the DAX RRS feed

  • Question

  • Hi Guys,

    I have a working calculation on Power BI that now needs to be changed/added to as the calculation it by using a date to calculate the results, I am really struggling trying to resolve this

    this function works up to the 11 May 2019 and from the 11 May it need to change below is what is the current one without a date, I  this for all entries prior to the 11 May and then add new measure from the 12 May

    ****current code now needs a cut off -( if [Updated]  < 11/05/2019

    NPS_Score = IF (
        ISBLANK ('NPS_Data'[String Value] ),
        "Blank",
        SWITCH (
            TRUE (),
             'NPS_Data'[String Value] >= 0
                && 'NPS_Data'[String Value] <= 6, "Detractors",
            'NPS_Data'[String Value] = 7
                || 'NPS_Data'[String Value] = 8, "Passives",
            'NPS_Data'[String Value] = 9
                || 'NPS_Data'[String Value] = 10, "Promoters"
        )) 

    *************new addition (if [Updated] >= 11 May

    'NPS_Data'[String Value] >= 0
                && 'NPS_Data'[String Value] <= 2, "Detractors",
            'NPS_Data'[String Value] = 3, "Passives",
            'NPS_Data'[String Value] = 4
                || 'NPS_Data'[String Value] = 5, "Promoters"

    sample data

    String value Created Updated Value
    5 2019-05-13 18:00:00 2019-05-04 15:23:39 1
    10 2019-05-10 15:12:32 2019-05-16 15:13:57 1
    5 2019-05-14 15:22:19 2019-05-14 17:51:25 1
    5 2019-05-20 14:10:10 2019-05-08 14:47:43 1
    4 2019-05-27 14:48:20 2019-05-27 14:50:28 1
    5 2019-05-26 14:44:18 2019-05-27 08:44:47 1
    5 2019-05-23 12:07:22 2019-05-23 14:25:25 1
    5 2019-05-25 17:44:18 2019-05-27 09:46:11 1
    4 2019-05-21 14:38:50 2019-05-02 07:40:15 1
    5 2019-05-21 14:41:47 2019-05-21 14:58:40 1
    5 2019-05-22 13:45:46 2019-05-22 13:57:40 1
    5 2019-05-22 14:48:16 2019-05-22 14:53:10 1
    5 2019-05-14 10:52:45 2019-05-14 10:56:46 1
    10 2019-05-08 17:28:38 2019-05-08 17:30:06 1
    5 2019-05-25 15:44:06 2019-05-27 15:09:41 1


    1

    Thanks










    Monday, May 27, 2019 2:30 PM

Answers

  • I'm assuming by the structure of your expression that this is a calculated column, so it should just be a matter of adding another IF statement.

    eg.

    NPS_Score = IF (
        ISBLANK ('NPS_Data'[String Value] ),
        "Blank",

    IF ( NPS_Data[Updated] >= DATE(2019,5,11),

    // Scoring for on or after 11 May 2019
    SWITCH (

            TRUE (),
             'NPS_Data'[String Value] >= 0
                && 'NPS_Data'[String Value] <= 2, "Detractors",
            'NPS_Data'[String Value] = 3, "Passives",
            'NPS_Data'[String Value] = 4
                || 'NPS_Data'[String Value] = 5, "Promoters"
        )
    // Scoring for before 11 May 2019

    ,    SWITCH (
            TRUE (),
             'NPS_Data'[String Value] >= 0
                && 'NPS_Data'[String Value] <= 6, "Detractors",
            'NPS_Data'[String Value] = 7
                || 'NPS_Data'[String Value] = 8, "Passives",
            'NPS_Data'[String Value] = 9
                || 'NPS_Data'[String Value] = 10, "Promoters"
        ))) 


    http://darren.gosbell.com - please mark correct answers

    Monday, May 27, 2019 10:49 PM