how to get 1 column/value from another table via DAX

[DataFunness]

I have a fact table with invoice information (Invoice Perspective) and a Working Days table that provided the current business day (workingday column) and the total number of working days in a month (workingdays2).  They are linked by a “year-month” column.  I am trying to pull in the current working day and total working days into measures in Invoice Perspective.  I am not sure on the DAX to use.  The measure below is now getting an error that says, “the expression refers to multiple columns. multiple columns cannot be converted to a scalar value” about bringing back an entire table.  There is only 1 row in the working days table for each year and month combination.  How do I link to that one row without errors? 

thank you in advance,

Matt

working day of month =

    VAR selectedYear = 'Calendar'[SelectedYear]

    VAR selectedMonth = 'Calendar'[SelectedMonth]

    RETURN CALCULATE(sumx('Working Days',

            FILTER('Working Days',

            'Working Days'[Month]=selectedMonth && 'Working Days'[Year]=selectedYear)))

Working Days table:

[Anonymous · Accepted solution]

Hi @DataFunness ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a calculated column.

Column = CALCULATE(SUM('Working Days'[Working day]),FILTER('Working Days','Working Days'[Year-Month]=EARLIER('Invoice'[Year-Month])))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[DataFunness]

@Pady Thank you for the message.  This is using NETWORKDAYS.  I am not looking to count days.  The table I am joining too has a count of days.  How do I use dax to "query" to get to the right value in my Working Days table?

[Pady]

Hope this video helps:

https://www.youtube.com/watch?v=DSkeFwkCcbY&list=PLApPcvU5-R26t4dylOnPy8A2-lr4C0C--&index=9