Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculate Not working

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? 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,Anonymous .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.

     

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    Anonymous 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Greg, thanks for sharing the useful links. 

      Sample data: 

      Risk Table:

      Project IDmsdyn_projectriskidpmotest_riskstatus_displaypmotest_riskstatus
      73ae842e-dbab-41c8-b20d-360e6c6cbaf9956b3f32-4fa7-ee11-a568-002248b17b71Closed646440002
      73ae842e-dbab-41c8-b20d-360e6c6cbaf9f01f3d1f-4fa7-ee11-a568-002248b17b71Active646440000
      73ae842e-dbab-41c8-b20d-360e6c6cbaf91ab63111-4fa7-ee11-a568-002248b17b71Closed646440002
      73ae842e-dbab-41c8-b20d-360e6c6cbaf93dbd91f8-4ea7-ee11-a568-002248b17b71Closed646440002
      73ae842e-dbab-41c8-b20d-360e6c6cbaf9b7e580e0-4ea7-ee11-a568-002248b17b71Closed646440002
      73ae842e-dbab-41c8-b20d-360e6c6cbaf91f09d039-3fa7-ee11-a568-002248b17b71Active646440000
      73ae842e-dbab-41c8-b20d-360e6c6cbaf95e78bf8f-3ea7-ee11-a568-002248b17b71Closed646440002
      73ae842e-dbab-41c8-b20d-360e6c6cbaf9b4621283-3ea7-ee11-a568-002248b17b71Closed646440002
      73ae842e-dbab-41c8-b20d-360e6c6cbaf9b1b6d442-3ea7-ee11-a568-002248b17b71Closed646440002

       

      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 IDmsdyn_projectriskidpmotest_riskstatus_displaypmotest_riskstatus
      73ae842e-dbab-41c8-b20d-360e6c6cbaf9f01f3d1f-4fa7-ee11-a568-002248b17b71Active646440000
      73ae842e-dbab-41c8-b20d-360e6c6cbaf91f09d039-3fa7-ee11-a568-002248b17b71Active646440000

       

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,Anonymous .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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi,Anonymous 
        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.