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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AlvinLy
Helper II
Helper II

Create a table as a variable in a measure

Hello,

 

I seem to be struggling to create a table as a variable and using the components of that table all within a measure.

 

So first of all I have a measure that is input as the value of a matrix visual in a PowerBI Report. The rows are the ranks and the columns are dates. What my measure does is that it goes to that column and fill in all activities that are of that date. I tested this measure with a created data table and it works fine and this is what the code looks like. Again no issue with the measure and the output:

 

 

Skyline= 
VAR CurrentRank = SELECTEDVALUE( 'Rank'[RNK] )
VAR SubSystemCount = DISTINCTCOUNT ( "Activity'[Name])
RETURN
IF (
    NOT ISBLANK ( CurrentRank ),
    IF (
        CurrentRank <= SubSystemCount,
        LASTNONBLANK (
            TOPN ( CurrentRank, VALUES ('Calc_Table'[Package Title]),'Calc_Table'[Package Title],ASC ),
            0
        )
    ),
    [Skyline Date]
)

 

 

This works fine, but it won't work for future scaling because I will eventually use DirectQuery mode for our database. Therefore 'Calc_Table' needs to be replaced properly. 

 

So my next step was to create a variable that is the created data table, but I keep getting the error that the table cannot be found, see sample code:

 

 

Skyline = 
VAR CurrentRank = SELECTEDVALUE( 'Rank'[RNK] )
VAR SubSystemCount = DISTINCTCOUNT ( 'Activity'[Name])
VAR PkgTitleTable = SUMMARIZECOLUMNS('Activity'[Name], "Package Title", [Package Title])
RETURN
IF (
    NOT ISBLANK ( CurrentRank ),
    IF (
        CurrentRank <= SubSystemCount,
        LASTNONBLANK (
            TOPN ( CurrentRank, VALUES ('PkgTitleTable'[Package Title]),'PkgTitleTable'[Package Title],ASC ),
            0
        )
    ),
    [Skyline Date]
)

 

 

Pretty much all i did was added a new variable named 'PkgTitleTable' which is essentially the same summarize code i used for my created data table, but I keep getting the warning: "Cannot find table 'PkgTitleTable'."

 

So basically, what am I doing wrong when i'm creating a table as a variable then referencing the columns in my table?

1 REPLY 1
lbendlin
Super User
Super User

Is PkgTitleTable part of your data model?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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