@bare,
OK, for a short post, you raised a ton of great questions. Let me try to answer them systematically:
- no, you can absolutely call/process created columns from a virtual table. However, doing so is a bit funky, since the naming conventions are not consistent with any other part of DAX. Instead of the typical ātableā[column] call structure, created columns in a virtual table get called like measures. I.e., [column]. This can get very confusing, which is why Marco Russo recommends (see article posted @ the end) as a best practice naming your virtual columns in the ātableā[column] format, so that when you call them within DAX they make like they are adhering to the standard call structure. Alternatively, you can do what most of us do which is to retrain ourselves when working with virtual tables to always ask whether something alone in brackets without an explicit table reference is a measure or a virtual column.
Hereās your revised virtual table code, rewritten as a variable within a measure that ultimately is called when calculating the AVERAGEX of the virtual score column:
virtual table AVERAGEX =
VAR vTable =
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(
'Employee Score',
'Employee'[Employee ID],
'Employee Score'[EmpScore],
'Employee Score'[Weights]
),
"current score", [Latest Score],
"latest date", MAX('Employee Score'[Month])
),
FILTER(
ALL(Employee),
PATHITEMREVERSE(Employee[Path], 2, TEXT) = "Emp B"
//CONTAINSROW()
)
)
VAR vAvgScore =
AVERAGEX(
vTable,
[current score]
)
RETURN
vAvgScore
- materializing virtual tables through the is a terrific way to debug virtual table code, but as you can see is not always reliable. When you materialized your table, it returned a number of rows, but when you did it as a variable it returned no rows and always led to BLANK() as your answer. The reason is that the measure occurs within context of the visual/page/report, while the table is calculated outside of context. In this case, we had a slicer on the page that was filtering the table down to only Emp ID = Employee B. But in the virtual table code, we had a filter condition that said
PATHITEMREVERSE(Employee[Path], 2, TEXT) = āEmp Bā
Taken together with the slicer, this means that we were looking for records in which Employee B was both the employer and the supervisor, which in this case was the null set. The materialized physical table however was created outside the context of the slicer, so it returned the proper records. The way to solve this is just to wrap the Employee table call in an ALL() statement to override the slicer. Once you do that, it returns the proper records.
- generally speaking, you definitely want to build your reports using virtual tables in your measures rather than calls to materialized physical tables calculated by DAX. Two major reasons: 1) doing the former keeps your data model clean and simple; 2) more importantly, virtual tables within a measure are completely dynamic, whereas materialized physical tables only get calculated upon load or manual refresh.
I hope this clarifies things, but these are pretty complex issues that for me at least only really "clicked"with a lot of practice. However, I completely agree with @sam.mckay that mastering virtual tables is what makes your DAX take a quantum leap forward.
Please let me know if any of this isnāt clear and/or if you have more questions. Solution file posted below,.
