Forum Discussion
Need help adding to a dax query
Hi Guys,
I have a 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 update this measure.The current 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 |
Hi MillyP ,
To create a combined one as below.
NPS_Score = IF ( ISBLANK ( 'NPS_Data'[String Value] ), "Blank", IF ( NPS_Data[Updated] <= DATE ( 2019, 05, 11 ), 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" ), IF ( NPS_Data[Updated] >= DATE ( 2019, 05, 11 ), 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" ) ) ) )
2 Replies
Hi MillyP ,
To create a combined one as below.
NPS_Score = IF ( ISBLANK ( 'NPS_Data'[String Value] ), "Blank", IF ( NPS_Data[Updated] <= DATE ( 2019, 05, 11 ), 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" ), IF ( NPS_Data[Updated] >= DATE ( 2019, 05, 11 ), 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" ) ) ) )Thank you for your help y-frfei-msft v works perfectly,
