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

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.

  • ercwebdev's avatar
    ercwebdev
    Frequent Visitor
    5 years ago

    I've done some work on this and managed to get something that will suffice and do the job. Thought I would share it with the community. 

     

    Created a new Table with the following:

    MyRedRiskTbl = 
FILTER(
Risks, 
Risks[RiskColour]="Red"
)

    This brings the Projects in that have a red. It seems to do a Distinct and doesn't duplicate them.

     

    Then added a new column in the Project Data to look up and check if there is a corresponding value in the virtual table:

    RedRiskCheckTwo = 
LOOKUPVALUE(
MyRedRiskTbl[RiskColour],----- Result Column Name
MyRedRiskTbl[Project ID],--- Search column 
'Project Data'[Project ID] ----- Search Value 
, Blank()--- Not Match with condition returns Blank
)

     

    This will then either insert 'Red' or leave it blank. I can then do some color formatting based on this.

    Here is how it looks:

     

    Thanks,

    Alan