DAX refer to column of a virtual table

[dicken]

Hi,

I i have the following

EVALUATE
VAR NB =
CALCULATETABLE ( Table2, FILTER ( ALL ( Table2 ), Table2[Column2] <> "B" ) )
RETURN
NB

is there a way to refer to 'column2' of the table NB, not the original so something like

EVALUATE
VAR NB =
   CALCULATETABLE ( Table2, FILTER ( ALL ( Table2 ), Table2[Column2] <> "B" ) )
RETURN 
   CALCULATETABLE( NB, FILTER(ALL( NB) , NB "Column1" = X ) )  
or 
CALCULATETABLE( NB, FILTER( ALL( NB)  , NB [Column2] = "A" ) )

I know this dos not work but I hope illustrates what I would like to do.

Richard

 

[Matt Allington]

Maybe, maybe not. The bigger question I have is, what are you trying to achieve? Many people coming from a traditional DB background try to solve DAX problems using table queries. This is rarely necessary because the Power BI visuals build the table structure for you; most of the time all you need to do is create the aggregation formula in the form of a measure.

 

[dicken]

Maybe, maybe not. The bigger question I have is, what are you trying to achieve? Many people coming from a traditional DB background try to solve DAX problems using table queries. This is rarely necessary because the Power BI visuals build the table structure for you; most of the time all you need to do is create the aggregation formula in the form of a measure.

I want to apply another filter, but it has to be to the filtered table, not the original or I will get values which intersect, are you saying you cannot
refer to a column in a virtual table as above,

 

[Matt Allington]

No, I’m not saying you can’t. You can when writing DAX queries. I cover it late in the second article of my series here An Introduction to DAX as a Query Language

I’m asking why are you creating a table in the first place. You can’t render the table in Power BI or Power Pivot, so why are you doing it? My guess (based on 10 years experience teaching people how to write DAX) is that your background intuition tells you this is the way to solve your problem, but in fact there is most likely a better, much simpler way. You generally do not need to use calculatetable and generally there is a better way, but I don’t know what you’re trying to do so I can only speculate

My total measure =
   CALCULATE ( sum(Table2[some numeric column]),
             FILTER ( ALL ( Table2 ), Table2[Column2] <> "B",
             Table2[Column1] = X ) )