Forum Discussion

ercwebdev's avatar
ercwebdev
Frequent Visitor
5 years ago
Solved

How to display info from another table?

Hi all,

I'm really not sure what structure of DAX to use for this problem. I have 2 tables, one with the Projects and another table with all of the risks for the projects, so it is a one to many relationship. 

 

I want to do a check of ALL the risks in the corresponding Risks table for each project to see if any are red. 

 

If so, the number of red risks should be counted, displayed in the Projects[RedRisk] column with the count number and the background color changed to red. Can use conditional formating to change the BG color. 

 

So putting it out there to see if someone has no doubt already done this and can share their method? 

I've tried lots things but can't not quite sure how to pull it off.

 

Have included an example of the tables to illustrate.

 

 

Thanks,

Alan  

DAX
  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago

    ercwebdev if you are creating a calculated column, try this code

    =COUNTROWS(FILTER(RELATEDTABLE(Risks),Risks[RiskColour]="Red"))

    if you are creating a measure, think the code of CNENFRNL might work well.

8 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi, ercwebdev , you might want to use such a measure in a table/matrix visualization in Power BI,

     

    RedRisk = COUNTROWS(FILTER(Risks, Risks[RiskColour]="Red"))

     

    • ercwebdev's avatar
      ercwebdev
      Frequent Visitor
      5 years ago

      Hi there CNENFRNL , thanks for that - pretty decent stab at it! What that is doing is looking through all the rows and counting the red (a total of 29) and doing it for every row and putting the value in. Just need it now to filter on ProjectID = ProjectID, but no idea how to do that with DAX?

       

      • CNENFRNL's avatar
        CNENFRNL
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Hi, ercwebdev , the most sophisticated part of DAX is filter propagation along with relationships among tables.

        As to your example, if 'Project Data'[ProjectID] is dragged and dropped in a table/matrix viz, Risks[ProjectID] is at first hand automatically filtered by 'Project Data'[ProjectID] given that there exists such a relationship, 'Project Data'[ProjectID] 1:M Risks[ProjectID].

         

        Better attach a mockup so that it's way much easier to get your issue done.

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    5 years ago

    ercwebdev if you are creating a calculated column, try this code

    =COUNTROWS(FILTER(RELATEDTABLE(Risks),Risks[RiskColour]="Red"))

    if you are creating a measure, think the code of CNENFRNL might work well.

    • ercwebdev's avatar
      ercwebdev
      Frequent Visitor
      5 years ago

      Hi Daniel,

      Thanks  - that works! Much appreciated. I knew there must be a way of doing this, but it's alwasy easy if you know how. Many thanks to both for your help. 

      Cheers

      Alan 

      • ercwebdev's avatar
        ercwebdev
        Frequent Visitor
        5 years ago

        Just a quick footnote on this - the 'accepted solution' filters the records based on IDs, i.e .it will only return records that have red risks, so it removes all other records instead of including them but giving them a value of zero.