Forum Discussion

andy808's avatar
andy808
Resolver I
2 years ago

Countrows, DistinctCount or Sum with a Filter to get a COUNT

I have 2 tables: [Projects] and [Issues], relationship is 1 [Project] to many [Issues] by Project ID

I thought this would be straight forward but I am unable to find the DAX formula to give the correct results.

Currently I have the table to the left and I need results on the right to produce COUNTS for a card visual stating there are 3 Open issues that involve 2 Projects: 

 

  -->>  

 

 

 

 

 

 

Need Help

2 Replies

  • chonchar's avatar
    chonchar
    Helper V
    2 years ago

    No. of Projects =
    Distinctcount(TableName[Project Name]
    )

    No. of Issues =
    distinctcount(TableName[Issueid]
    )

    Then add those measures into the cards. 

     

    Good luck, 

    Cam

    • andy808's avatar
      andy808
      Resolver I
      2 years ago

      Thannk you for your reply, however using any of these measures in a CARD - doesn't take into consideration the Closed or Open filter.

       

      I use the measurebelow in a table visual and I get the correct answer of 8, but the card does not allow me to use this filter

       

      CountIssuesNotClosed =
      IF([IssueCount] > 0 && CALCULATE(COUNTROWS('Issues'), KEEPFILTERS(Issues[Status] <> "(3) Closed"
            )),
         1, 0
        )
       
      My table results with the measure on the bottom table

      So I have formatted the 8 in the table to get a card looking visual but was hoping for a better solution