DAX LOOKUP IF AND COUNT

[1984]

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 🙂

[lbendlin · Accepted solution]

Read about RELATEDTABLE. That allows you to look into the "many"  side of a relationship.

[v-kaiyue-msft · Accepted solution]

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.

[lbendlin · Accepted solution]

Read about RELATEDTABLE. That allows you to look into the "many"  side of a relationship.