Forum Discussion
Helper IITest calculated column as query in smaller table with SELECTEDTABLE
Hi All,
I would really like to test some more complex calculated column in Dax Studio or Tabular Editor.
This calculated column depends on multiple other columns in the same table which is fairly wide and ha a lot of additional columns I do not need to see to test the logic og my calculated column.
So my Idea was to create a smaller test table as "playground" in Tabular Editor with the following Syntax:
VAR Playground =
CALCULATETABLE(
SELECTCOLUMNS(
'Projects',
"Needed Info 1", 'Projects'[Needed Info 1],
"Needed Info 2", 'Projects'[Needed Info 2],
"Needed Info 3", 'Projects'[Needed Info 3],
),
'Projects'[Owner] = "John",
'Projects'[Source] = "BWH"
)
This allows me to Filter the Table to some relevant portion, make it a lot slimmer and still have the same grain as my original project table.
Now I would like to leverage the row context of the ADDCOLUMN() function to add my new calculated column for testing:
ADDCOLUMNS(
Playground,
"Test Calc Column", 'Projects'[Needed Info 1]
)
This results in an error an error saying that no singular value could be found for column "Needed Info 1" in table "Projects".
What am I getting wrong here ?
Hi JoachimSA
If you want to add a new column, you can only add the columns already in the current table, you can not add columns in another table, to add columns in other tables you need to use expressions such as sum(), calculate(), etc , please refer to the following link:
How to use AddColumns function in DAX and Power BI - RADACAD
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi JoachimSA
Did you use "return", e.g
VAR Playground = CALCULATETABLE( SELECTCOLUMNS( 'Projects', "Needed Info 1", 'Projects'[Needed Info 1], "Needed Info 2", 'Projects'[Needed Info 2], "Needed Info 3", 'Projects'[Needed Info 3], ), 'Projects'[Owner] = "John", 'Projects'[Source] = "BWH" ) return ADDCOLUMNS( Playground, "Test Calc Column", [Needed Info 1])Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi JoachimSA
If you want to add a new column, you can only add the columns already in the current table, you can not add columns in another table, to add columns in other tables you need to use expressions such as sum(), calculate(), etc , please refer to the following link:
How to use AddColumns function in DAX and Power BI - RADACAD
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- JoachimSA
Hi,
it seems I got ist to work by adressing the selectedcolumns by their new name without the original table name in the front:
ADDCOLUMNS( Playground, "Test Calc Column", [Needed Info 1] )If I had used a Summarizecolumns to create the Playground table I would have to use the full original column name it seems.
Hi JoachimSA
Did you use "return", e.g
VAR Playground = CALCULATETABLE( SELECTCOLUMNS( 'Projects', "Needed Info 1", 'Projects'[Needed Info 1], "Needed Info 2", 'Projects'[Needed Info 2], "Needed Info 3", 'Projects'[Needed Info 3], ), 'Projects'[Owner] = "John", 'Projects'[Source] = "BWH" ) return ADDCOLUMNS( Playground, "Test Calc Column", [Needed Info 1])Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- JoachimSA
yes I did, works great 🙂
