Dax Measure Error: Expression Contains multiple Columns??

[powerbignc]

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...

[powerbignc]

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>,

    ...,

)

[AlexisOlson]

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
)

[powerbignc]

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...?

[AlexisOlson]

The separate columns are in separate filters in this case (separated with "," instead of "&&"). They are two independent conditions <filter1> and <filter2> that both get applied.

[AlexisOlson · Accepted solution]

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" )
    )
)

[powerbignc]

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....

[AlexisOlson]

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.

[powerbignc]

Perfect. Yeah my fact table has like over 180 million rows, so was worried about performing a filter on such large table.... Just tested the new formula and it works! Thanks!