Forum Discussion

kishoresakamuri's avatar
kishoresakamuri
Advocate II
9 years ago
Solved

expression in power bi

Hi,

can any one help on this how to create expression in power BI.

 

Sum(iif([Status] = "N/A" Or [Status] = "Out Of Scope" Or lcase([Status] = "de-scoped rbt" Or [Status]= "Deferred" Or [Status] = "Invalid", 0, 1))
- Sum([Column]) + Sum(iif([Status]= "N/A"
 Or [Status]= "Out Of Scope" Or lcase([Status]) = "de-scoped rbt" Or [Status]= "Deferred" Or [Status] = "Invalid", 1, 0))

Custom Visuals
Gateways
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi kishoresakamuri,

     

    Please do not add double quotes around the number 1 and 0. Please check the DAX in my orignal post again.

     

    Best Regards,
    Qiuyun Yu

7 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi kishoresakamuri,

     

    Please try to create a measure use SUMX() function like below:

     

    =SUMX('<TableName>', IF([Status] = "N/A" || [Status] = "Out Of Scope" || [Status] = "de-scoped rbt" || [Status]= "Deferred" || [Status] = "Invalid", 0, 1))
    - Sum([Column]) + SUMX('<TableName>', IF([Status] = "N/A" || [Status] = "Out Of Scope" || [Status] = "de-scoped rbt" || [Status]= "Deferred" || [Status] = "Invalid", 1, 0))

     

    If above measure doesn't meet your requirement, please share sample data and expected results for our analysis.

     

    Best Regards,
    Qiuyun Yu

    • kishoresakamuri's avatar
      kishoresakamuri
      Advocate II
      9 years ago

      HI

       

      Error: The function SUMX takes an argument that evaluates to numbers or dates and cannot work with values of type String.

       

      This is the error  i am facing when i create  below measure .

       SUMX(dim_test_lab,IF(dim_test_lab[current_user_defined_status]="N/A" ||dim_test_lab[current_user_defined_status]="Out Of Scope" || dim_test_lab[current_user_defined_status]="de-scoped rbt" || dim_test_lab[current_user_defined_status]="Deferred" || dim_test_lab[current_user_defined_status]="Invalid","0","1")) -SUM(dim_test_set[baseline_number]) + SUMX(dim_test_lab,IF(dim_test_lab[current_user_defined_status]="N/A" ||dim_test_lab[current_user_defined_status]="Out Of Scope" || dim_test_lab[current_user_defined_status]="de-scoped rbt" || dim_test_lab[current_user_defined_status]="Deferred" || dim_test_lab[current_user_defined_status]="Invalid","1","0"))

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        9 years ago

        Hi kishoresakamuri,

         

        Please do not add double quotes around the number 1 and 0. Please check the DAX in my orignal post again.

         

        Best Regards,
        Qiuyun Yu