expression in power bi

[kishoresakamuri]

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))

[v-qiuyu-msft]

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]

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 · Accepted solution]

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]

Hi,

How can rank function defined in power bi actually i am new to Power Bi.

RANK() OVER (PARTITION BY TC.Project_id, TC.Release_id,TC.release_cycle_id, RELC.sprint_name, C.test_set_name,C.test_set_id ORDER BY TC.test_name,C.test_set_id) as baselined_test_rank

Regards,

Kishore Sakamuri.

[v-qiuyu-msft]

Hi @kishoresakamuri,

As this issue is different from the original issue which should be a second issue, please post it as a new thread. Thanks for your understanding and support. 

By the way, you can find the syntax of DAX RANKX() from here https://msdn.microsoft.com/en-us/library/gg492185.aspx .

Best Regards,
Qiuyun Yu

[kishoresakamuri]

Hi

Is there any live chat to calrify doubts.

[v-qiuyu-msft]

This is forum support . If you have any question about this issue, please feel free to post.

If you want to dedicated support, you can open a support ticket at the bottom of this site: https://powerbi.microsoft.com/en-us/support/ .

Best Regards,
Qiuyun Yu