Calculated Column: Find value between dates from another table

[jguercio]

Hi All,

Can someone help me write this so it does not produce this error?

"The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression."

Intent:

I am trying to get a value from a fact table and put it in a calculated column of a summary table. The granularity of the fact table is multiple records in a day. The granularity of the summary table is monthly. Essentially, I need to know who is the primary worker between the two dates on the summary table. Sometimes the result has more than one worker, so I want the one that was most frequent during the time. The following works fine in Tabular Editor when I hard code the right-side variables of the CALCULATETABLE filters:

Primary Worker = 
VAR Table1 = 
CALCULATETABLE(
    SUMMARIZE(
        'FACT-Service',
        'FACT-Service'[Primary Worker on Contact Date],
        "CountPW", COUNT( 'FACT-Service'[Primary Worker on Contact Date] )
    ),
    'FACT-Service'[Bridge ID] = 'BH Monthly Billing Table'[Program-Client ID]
    && 'FACT-Service'[Contact Date] >= 'BH Monthly Billing Table'[Period Begin]
    && 'FACT-Service'[Contact Date] <= 'BH Monthly Billing Table'[Period End]
)

RETURN
      SELECTCOLUMNS(
      TOPN( 1, Table1, [CountPW]),
      "Worker", 'FACT-Service'[Primary Worker on Contact Date]
  )

[jguercio]

Here is a sample file of the issue. Thanks for your assistance:

PBI Sample File 

[V-lianl-msft]

Create a relationship like below and rewrite the formula:

Primary Worker = 
VAR Table1 = 
CALCULATETABLE(
    SUMMARIZE(
        'FACT-Service',
        'FACT-Service'[Primary Worker on Contact Date],
        "CountPW", COUNT( 'FACT-Service'[Primary Worker on Contact Date] )
    ),FILTER('FACT-Service',
    RELATED('Monthly Billing Table'[ProgramID-ID]) = 'FACT-Service'[Bridge ID]
    && 'FACT-Service'[Contact Date] >= RELATED('Monthly Billing Table'[Period Begin])
    && 'FACT-Service'[Contact Date] <= RELATED('Monthly Billing Table'[Period End]
)))

RETURN
      SELECTCOLUMNS(
      TOPN( 1, Table1, [CountPW]),
      "Worker", 'FACT-Service'[Primary Worker on Contact Date]
  )

Is this the result you want?

[jguercio · Accepted solution]

I get the following error when I run the solution:
The column 'Monthly Billing Table[ProgramID-ID]' either doesn't exist or doesn't have a relationship to any table available in the current context.

However, this solution seems to work:

Primary Worker = 
var ProgClientID = 'BH Monthly Billing Table'[Program-Client ID]
var PD_Begin = 'BH Monthly Billing Table'[Period Begin]
var PD_End = 'BH Monthly Billing Table'[Period End]
VAR Table1 = 
CALCULATETABLE(
ADDCOLUMNS(
    SUMMARIZE(
        'FACT-Service',
        'FACT-Service'[Primary Worker on Contact Date],
        "CountPW", COUNT( 'FACT-Service'[Primary Worker on Contact Date] )
    ), "WorkerName",'FACT-Service'[Primary Worker on Contact Date] ),
    'FACT-Service'[Bridge ID] =ProgClientID
    && 'FACT-Service'[Contact Date] >= PD_Begin
    && 'FACT-Service'[Contact Date] <= PD_End
)

var W_name_row = TOPN(1,table1, [CountPW], DESC)

RETURN
MAXX(W_name_row, [WorkerName])

 I appreciate your speedy attempt, thank you.

[V-lianl-msft]

Please refer to this blog

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

and provide your expected results.

[PaulDBrown]

SELECTCOLUMNS returns a table which will return an error as you are seeing. You could try wrapping it in COUNTROWS and use the measure to filter the values in a visual where the outcome = 1

so...

Primary Worker = 
VAR Table1 = 
CALCULATETABLE(
    SUMMARIZE(
        'FACT-Service',
        'FACT-Service'[Primary Worker on Contact Date],
        "CountPW", COUNT( 'FACT-Service'[Primary Worker on Contact Date] )
    ),
    'FACT-Service'[Bridge ID] = 'BH Monthly Billing Table'[Program-Client ID]
    && 'FACT-Service'[Contact Date] >= 'BH Monthly Billing Table'[Period Begin]
    && 'FACT-Service'[Contact Date] <= 'BH Monthly Billing Table'[Period End]
)

RETURN
      COUNTROWS(
SELECTCOLUMNS(
      TOPN( 1, Table1, [CountPW]),
      "Worker", 'FACT-Service'[Primary Worker on Contact Date]
  )
)

[amitchandak]

@jguercio ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

We can think of creating a column if that can work

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[jguercio]

How do I share the sample .pbx file? I get an error that .pbx is not supported.