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.

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