Table variable cannot be used in current context because a base table is expected by inflow calc

[MiKeZZa]

Hi all,

I want to calculate an inflow. What I do is this:

Inflow Inside 3 = 

VAR OfficePrevious = CALCULATETABLE(SUMMARIZE(DataFte,DataFte[Office Code],DataFte[EmpId])
                    --,PREVIOUSMONTH('Date'[Date]), ALL('Date')
                    ,'Date'[Month Number] = 4
                    ,DataFte[Internal] = "Y"
                    ,ALLEXCEPT(DataFte, DataFte[EmpId], DataFte[Office Code]))
                    
VAR OfficeCurrent = CALCULATETABLE(SUMMARIZE(DataFte,DataFte[Office Code],DataFte[EmpId])
                    ,'Date'[Month Number] = 5
                    ,DataFte[Internal] = "Y"
                    ,ALLEXCEPT(DataFte, DataFte[EmpId], DataFte[Office Code]))

VAR ResTable = EXCEPT(OfficePrevious, OfficeCurrent)

RETURN ResTable

As you can see my period selection is set not dynamically, that will come later. But my issue now is that I calculate 2 tables and with Except I create a table with the diff (this is the inflow if I'm correct). But I can't simply return the Restable, so I have to do something with it. But when I use it in a GROUPBY or SUMMARIZE or CALCULATE the error that comes is 'Table variable cannot be used in current context because a base table is expected'.

So is there anybody who can help me to make a distinct count of the EmpId field in the results in the ResTable table which content is (by example):

OfficeCode    EmpId
23                  4

25                  7

My PBIX example file is located here; https://easyupload.io/i1xygt and the measure is called Inflow Inside 3.

[MFelix · Accepted solution]

Hi @MiKeZZa ,

Measure return a single value, meaning that you cannot make a measure that returns a table, this is the case that you are getting you are creating one table that returns the values.

In your case what you need to do is to count the number of rows for each table and return the result.

The result of your current table is:

The result of your previous table is:

If you llok at the values you have

Office	Current	Previous	Diff
20	1 row	1 row	0
21	1 row	1 row	0
22	1 row	1 row	0
23	0 rows	1 row	-1
24	2 rows	0 rows	2
25	0 row	1 row	-1
26	25 rows	0 rows	2

If you redo your measure to the code below you get the correct result:

Inflow Inside 3 = 

VAR OfficePrevious = CALCULATETABLE(SUMMARIZE(DataFte,DataFte[Office Code],DataFte[EmpId])
                    --,PREVIOUSMONTH('Date'[Date]), ALL('Date')
                    ,'Date'[Month Number] = 4
                    ,DataFte[Internal] = "Y"
                    ,ALLEXCEPT(DataFte, DataFte[EmpId], DataFte[Office Code]))
                    
VAR OfficeCurrent = CALCULATETABLE(SUMMARIZE(DataFte,DataFte[Office Code],DataFte[EmpId])
                    ,'Date'[Month Number] = 5
                    ,DataFte[Internal] = "Y"
                    ,ALLEXCEPT(DataFte, DataFte[EmpId], DataFte[Office Code]))

VAR ResTable = COUNTROWS(OfficeCurrent) - COUNTROWS( OfficePrevious)

RETURN ResTable