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?
