Forum Discussion
Dax Measure Error: Expression Contains multiple Columns??
I am getting the following error when trying to view data. See below error message...
Here is my DAX Measure....
Brand Sales TY:=
CALCULATE([Ttl Sales Rtl$ TY],
AB_ITEM_MASTER[PRIVATE_LABEL]="Y" ||
(AB_ITEM_MASTER[ITEM_PARTY]="1st Party" || AB_ITEM_MASTER[ITEM_PARTY]="2nd Party")
)
Thoughts? Below is a view of the model, AA_Daily_Sales_Detail is the "Fact" table and I am trying to calculate based on dimension fields in ITEM_Master Dim table...
You cannot write a single simple filter argument referencing multiple columns. That is, having the "1st Party" or "2nd Party" is fine since you're referencing the same column [ITEM_PARTY] but you can't combine that with a filter on [PRIVATE_LABEL] in a simple True/False filter. See here for more detail:
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
In order to do this, you need to write a table filter instead.
Brand Sales TY := CALCULATE ( [Ttl Sales Rtl$ TY], FILTER ( AB_ITEM_MASTER, AB_ITEM_MASTER[PRIVATE_LABEL] = "Y" || ( AB_ITEM_MASTER[ITEM_PARTY] = "1st Party" || AB_ITEM_MASTER[ITEM_PARTY] = "2nd Party" ) ) )
8 Replies
You cannot write a single simple filter argument referencing multiple columns. That is, having the "1st Party" or "2nd Party" is fine since you're referencing the same column [ITEM_PARTY] but you can't combine that with a filter on [PRIVATE_LABEL] in a simple True/False filter. See here for more detail:
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
In order to do this, you need to write a table filter instead.
Brand Sales TY := CALCULATE ( [Ttl Sales Rtl$ TY], FILTER ( AB_ITEM_MASTER, AB_ITEM_MASTER[PRIVATE_LABEL] = "Y" || ( AB_ITEM_MASTER[ITEM_PARTY] = "1st Party" || AB_ITEM_MASTER[ITEM_PARTY] = "2nd Party" ) ) )Question on the FILTER operator, should the table "AB_ITEM_MASTER" be the one filtered since it's a dimension table OR should you be filtering the "AA_Daily_Sales_Detail" since that is the fact table and the same table that has the values column which [Ttl Sales Rtl$ TY] is referencing? Here is the equation for [Ttl Sales Rtl$ TY].
Ttl Sales Rtl$ TY:=SUM(AA_Daily_Sales_Detail[SalesRevenue])
Just curious for my own understanding since in my mind I am thinking logically I would filter the fact table not the dimension table....
One of the main purposes of a dimension table is to filter on. It's typically best practice to filter on a dimension table instead of the fact table if there is a choice between the two.
Another question, but similar.... in the example I gave you applied a filter to one dimension table, by filtering on columns within that table.... HOWEVER what if I gave an example where I am needing to filter across multiple tables.... I.e. two different dim tables below
[AA_Daily_Sales_Detail].[CUST_TYPE] = 'R' AND [AA_Daily_Sales_ID].[RegisterNbr] < 90)
What would the logic look like here?something like this?
CALCULATE (<table expression>,<filter1>,<filter2>,...,)AND filtering is easier since they can be defined separately.
It would probably look something like this:
CALCULATE ( [Measure], AA_Daily_Sales_Detail[CUST_TYPE] = "R", AA_Daily_Sales_ID[RegisterNbr] < 90 )But in this answer you are using different columns from different tables in a calculate statement? Why would you not get the same error as before? i.e. Expression contains multiple columns, etc...?
