How can I write a COUNTROWS measure using value in a related table?

[Anonymous]

I have two tables. One table is my Calendar Table, the other table is my Data table, and they are both related by 1-* on a Date column. I need to create a dynamic count of rows in the Data table as long as the date on which that event occurred is marked as a "Work-Day" in the Calendar Table. I want to create a measure to accomplish this but cannot figure out how to do so without resorting to CALCULATE, which does not dynamically update when selections are made on the various visuals on the canvas.

With the data below, the count should be 1.

Calendar

Date              Category

1/1/2019           Off

1/2/2019           Off

1/3/2019       Work-Day

Data

Date                Event

1/1/2019        Holiday

1/2/2019        Holiday

1/3/2019       Conference

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a measure

Workday Count = 
CALCULATE(     
    COUNTROWS( Data ),
   'Calendar'[Category] = "workday"
)

If it doesn't meet your needs, please tell me your expected result.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jthomson]

What are you doing to make it not "dynamically update"? I'd have thought either a calculate countrows of your data table where the related value is work-day ought to work, either that or just join in the category column into your data table in Power Query and then not use the calendar table at all?

[parry2k]

@Anonymous try this measure

Workday Count = 
CALCULATE(     
    COUNTROWS( Data ),
    'Calendar'[Category] = "Work-Day"
)