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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
modi123p
Helper III
Helper III

Unable to use virtual tables with variables in PBI Desktop

Hi,

I m unable to use virtual tables with variables while creating measures.

Im using PBI Dektop (Feb 2021) and found it strange.

Any configuration that I need to enable?

 

Please refer the measures below:

 

//This Works:

MonthSlicerFilter =
VAR virtualTable = SUMMARIZECOLUMNS(Month_Year[Month], "InRange", [YTD Month Validator])
RETURN MIN(virtualTable[Month])
 

//This doesnt:

Error message --> Table variable 'Virtual table' cannot be used  in current context because a base table is expected.

MonthSlicerFilter =
VAR virtualTable = SUMMARIZECOLUMNS(Month_Year[Month], "InRange", [YTD Month Validator])
VAR _minVal = MIN(virtualTable[Month])
RETURN _minVal

 

Kindly ignore what I m trying to do in the above measures. Its only a trial 🙂

 

Thanks for your help.

 

Regards,
Mannu

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @modi123p 

SQLBI have written a great article on this so rather than retype everything here, here's the link

Table and column references using DAX variables - SQLBI

 

Basically, assigning a table to a variable does not make the variable a table.  So you can't reference columns 'inside' the variable as you would with a table.

The article describes a couple of workarounds to this problem.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

2 REPLIES 2
PhilipTreacy
Super User
Super User

Hi @modi123p 

SQLBI have written a great article on this so rather than retype everything here, here's the link

Table and column references using DAX variables - SQLBI

 

Basically, assigning a table to a variable does not make the variable a table.  So you can't reference columns 'inside' the variable as you would with a table.

The article describes a couple of workarounds to this problem.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Thank you @PhilipTreacy 

You saved my day.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.