How to filter in DAX using another column in a table?

[nirupreddy]

Suppose i have two tables table and tableB in power bi

"CALCULATE ( expression, table[column] = value )"

In the above DAX can I use tableB[column2] instead of value?

[v-frfei-msft]

Hi @nirupreddy,

I made one sample for your reference. If it doesn't meet your requirement, kindly share your sample data and excepted result to me.

Measure =
CALCULATE (
    SUM ( Table1[Column2] ),
    FILTER ( Table1, Table1[Column1] = MAX ( Table2[Column1] ) )
)

Regards,

Frank

[v-frfei-msft]

Hi @nirupreddy,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

[MFelix]

Hi @nirupreddy,

This is possible using the @jiglow3501 sintax however for thar code to work you need that both tables are related if that by a commob field.

Are your tw tables related or not?

Regards,
MFelix

[nirupreddy]

Hi @jiglow3501 and @MFelix,

Both My tables are related. To be more specific I have my fact table that has YYYYWW = year*100+week, year and week as columns and few measures.
And My date dimension has following columns
YYYYWW = year*100+week,quarter,period, starting date of week, ending date of week, and a column LY_YYYYWW that gives a  52 weeks earlier (year*100 + week) value
And i am trying to get this years measure and 52 weeks earlier measure to compare aginst.
I can't use time intelligence functions as this is a custom calendar.
So, i am trying 

measure_ly = CALCULATE ( expression, table.YYYYWW = tableb.LY_YYYYWW)"

is it possible.

[MFelix]

Hi @nirupreddy,

As said you can use the filter function looking at your formula for year something like this should work


Measure = CALCULATE (EXPRESSION ; FILTER(ALL(Table[YYYYWW]); Table[YYYYWW] = MAX (Table[YYYYWW] -100) ))

Regards,
MFelix

[nirupreddy]

Hi Felix,

Thank you for the response. I want to to avoid that caluclation as i already have that value in tableB as LY_YYYYWW and want to use this column.

[MFelix]

To filter the information it wil be faster with the calcumation. You should skip the previous year calculation as a column in your custm calendar adds size and complexity in your model, making the calculation on the measure ot will only be calculated at request and not every tine uou load your model and/or refresj.

MFelix

[nirupreddy]

Thank You @MFelix, 
I do get what you are saying but my previous year calculation is not that simple as we have to take leap year and 53 weeks into consideration. So its not possible to calculate on the fly. Hence i have pre poulated in my dimension

[MFelix · Accepted solution]

Hi @nirupreddy,

Understand your requirements but believe that your setup.is not the most efficient and with bedt options, just a question if you have a leap.year with 53 weeks how does that compare with previous year, to week 52 or week 1 of current year?

However I believe that you date table could be setup in a different way try the following measure:

Measure = CALCULATE (EXPRESSION ; FILTER(ALL(Table[YYYYWW]); Table[YYYYWW] = MAX (Table[LYYYYYWW] ) ))

Not on computer so didn't try it but should work.

Regards
MFelix

[jiglow3501]

in this instance, it is retrieving max minus 30 for total sales.  Make sure that your tables has cardinality.

30Day =
CALCULATE([Total Sales],
FILTER(ALL(Dates),
Dates[Date] > MAX(Dates[Date])-30))