Forum Discussion
Helper IDAX LOOKUP IF AND COUNT
Hi
I've been away from DAX for some time and trying to create a column with a calculation.
I have two tables with a 1:many relationship.
I would like to create a column in Table 1 (1 side) that lookup in table 2 at how many rows for each ID have the status "Complete" in the status column contained in table 2?
Like I say new returner so probably a simple fix pls be kind 🙂
Read about RELATEDTABLE. That allows you to look into the "many" side of a relationship.
Hi 1984 ,
I want to acknowledge valuable input provided by lbendlin . Their initial ideas help guide my approach. However, I noticed that more details are needed to fully understand this issue.
I created two tables and created a one-to-many relationship for them.
Create a calculated column in the "table" table for counting.StatusCount = VAR_count= CALCULATE( COUNTROWS('Table 2'), RELATEDTABLE('Table 2'), 'Table 2'[status] = "Complete" ) RETURN IF(_count = BLANK(),0,_count)If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- lbendlin
Super User
Read about RELATEDTABLE. That allows you to look into the "many" side of a relationship.
Hi 1984 ,
I want to acknowledge valuable input provided by lbendlin . Their initial ideas help guide my approach. However, I noticed that more details are needed to fully understand this issue.
I created two tables and created a one-to-many relationship for them.
Create a calculated column in the "table" table for counting.StatusCount = VAR_count= CALCULATE( COUNTROWS('Table 2'), RELATEDTABLE('Table 2'), 'Table 2'[status] = "Complete" ) RETURN IF(_count = BLANK(),0,_count)If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.