How to add a filter from another table to a measure created in the date table?

[Anonymous]

Hi,

I'm trying to set up a visual that would calculate the number of workdays depending on the country of origin.

I set up the following date table (attaching a printscreen of a specific instance where the number of workdays will be different between 2 countries).

I also set up a measure in the date table that would return the number of workdays for a given period but cannot seem to add a country filter (per Absentee ID located in another table).

The bar for Absentee ID 185 should be 0 as that ID is associated with the country SK and all 3 filtered dates are days off.

Can you advise how to best go about this?

Thanks,

Adam

[Anonymous]

Works perfectly. Thank you so much, @Anonymous!

[Anonymous · Accepted solution]

Hi, @Anonymous 

I am glad to help you.  

According to your description, you want to know how to add a filter from another table to a measure created in the date table? 

If I understand you correctly, then you can refer to my solution. 

Your Measure doesn't filter for country, so it will return the same results regardless of the country associated with the absence ID. 

Since you didn't provide specific test data, I created a simple dataset based on your description: 

You can then try to rewrite Measure into the following formula: 

TestWorkingDays = 
VAR SelectedCountry =
    SELECTEDVALUE ( 'AbsenteeTable'[Country] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Date Table' ),
        'Date Table'[Working Day CZ] = 1
            && SelectedCountry = "CZ"
            || 'Date Table'[Working Day SK] = 1
            && SelectedCountry = "SK"
    )

Here is my test result: 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.