Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MiKeZZa
Post Patron
Post Patron

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

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.

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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:

 

MFelix_1-1623928908760.png

 

 

The result of your previous table is:

MFelix_0-1623928878183.png

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

MFelix_2-1623929106067.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

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:

 

MFelix_1-1623928908760.png

 

 

The result of your previous table is:

MFelix_0-1623928878183.png

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

MFelix_2-1623929106067.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors