Calculate Not working

[mm_merchant]

Hi All, 

I have 2 tables: Projects and Risks where a 1:many relationship exists from Projects:Risks. The Risk table has a status column which is of type Choice ( 3 values): Active, Closed, Postponed

I need to show "Active" risks on a card visual in my report. The DAX I have is:

TestActive Risks =

VAR _totalrisks =

    COUNT ( Risk[Project ID])

VAR _onlyactive =

CALCULATE( _totalrisks,  Risk[pmotest_riskstatus] = "646440000", Risk[pmotest_riskstatus_display] = "Active")

RETURN    

    _onlyactive

This is returning all risks for that particular Project ID, instead what I need is filtered to only show "Active" ones as shown below:

 On my visual, I have a slicer to select the Project from the Project Table, I expect it should show only the "Active" risks for that particular project. 

What am I doing wrong here? 

[Anonymous · Accepted solution]

Hi,@mm_merchant .Hello,@Greg_Deckler,thanks for your concern about this issue.
And I would like to share some additional solutions below
I am glad to help you
There seems to be an error in your dax code, you can try this measure:

M_result = 
VAR _onlyactive =
CALCULATE(COUNT ( Risk[Project ID]), FILTER( 'Risk',Risk[pmotest_riskstatus] = "646440000" && Risk[pmotest_riskstatus_display] = "Active"))
RETURN    
    _onlyactive

In the calculate () function, the expression to be evaluated should not use variables as much as possible.

here is my test result

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mm_merchant]

Hello Carson,

I updated the DAX with your suggestion and I got it to work as expected. I tried the variation without the Filter function like this & it also worked:

VAR _getActiveRisks1 = CALCULATE( COUNT(Risk[Project ID]), Risk[pmotest_riskstatus] = "646440000", Risk[pmotest_riskstatus_display] = "Active")

thanks a lot for testing on your end. 

[Anonymous]

Hi,@mm_merchant 
I'm glad you solved your problem, if you have any other questions about power BI, you are welcome to share them on the forum as it will help more users with similar problems!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@mm_merchant Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[mm_merchant]

Hi Greg, thanks for sharing the useful links. 

Sample data: 

Risk Table:

Project ID	msdyn_projectriskid	pmotest_riskstatus_display	pmotest_riskstatus
73ae842e-dbab-41c8-b20d-360e6c6cbaf9	956b3f32-4fa7-ee11-a568-002248b17b71	Closed	646440002
73ae842e-dbab-41c8-b20d-360e6c6cbaf9	f01f3d1f-4fa7-ee11-a568-002248b17b71	Active	646440000
73ae842e-dbab-41c8-b20d-360e6c6cbaf9	1ab63111-4fa7-ee11-a568-002248b17b71	Closed	646440002
73ae842e-dbab-41c8-b20d-360e6c6cbaf9	3dbd91f8-4ea7-ee11-a568-002248b17b71	Closed	646440002
73ae842e-dbab-41c8-b20d-360e6c6cbaf9	b7e580e0-4ea7-ee11-a568-002248b17b71	Closed	646440002
73ae842e-dbab-41c8-b20d-360e6c6cbaf9	1f09d039-3fa7-ee11-a568-002248b17b71	Active	646440000
73ae842e-dbab-41c8-b20d-360e6c6cbaf9	5e78bf8f-3ea7-ee11-a568-002248b17b71	Closed	646440002
73ae842e-dbab-41c8-b20d-360e6c6cbaf9	b4621283-3ea7-ee11-a568-002248b17b71	Closed	646440002
73ae842e-dbab-41c8-b20d-360e6c6cbaf9	b1b6d442-3ea7-ee11-a568-002248b17b71	Closed	646440002

The Project ID comes from Project Table and it is 1: Many relationship between Projects:Risks. 

On my visual I have the Project ID (or Project Name) slicer and what I expect is that the card visual should show only the Risks that have "pmotest_riskstatus_display" = "Active".

Project ID	msdyn_projectriskid	pmotest_riskstatus_display	pmotest_riskstatus
73ae842e-dbab-41c8-b20d-360e6c6cbaf9	f01f3d1f-4fa7-ee11-a568-002248b17b71	Active	646440000
73ae842e-dbab-41c8-b20d-360e6c6cbaf9	1f09d039-3fa7-ee11-a568-002248b17b71	Active	646440000