Forum Discussion

kishoresakamuri's avatar
kishoresakamuri
Icon for Advocate II rankAdvocate 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] =...
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

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity 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
    Icon for Advocate II rankAdvocate 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
      Icon for Community Support rankCommunity 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

      • kishoresakamuri's avatar
        kishoresakamuri
        Icon for Advocate II rankAdvocate II
        9 years ago

        Hi

         

        Is there any live chat to calrify doubts.