Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ercwebdev
Frequent Visitor

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.

 

2020-11-10 11_13_24-Risks layout - Excel.png

 

Thanks,

Alan  

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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

View solution in original post

8 REPLIES 8
wdx223_Daniel
Super User
Super User

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

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 

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. 

CNENFRNL
Community Champion
Community Champion

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"))

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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?

 

RedRisk.png

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.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Hi,

Ok, have made a quick mockup and zipped (.pbix and Excel) if you want to download and try having a play:

 

https://1drv.ms/u/s!AtRktPvcDTo7dbfRYaTFut2IjhQ?e=NUDcJo

 

Many thanks,

A

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:

 

2020-11-11 16_23_42-Risks Sample - Power BI Desktop.png

Thanks,

Alan 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.