cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nirupreddy
Frequent Visitor

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

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?

1 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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

10 REPLIES 10
v-frfei-msft
Community Support
Community Support

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

Capture.PNG

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @nirupreddy,

 

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

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
MFelix
Super User
Super User

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

 

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



jiglow3501
Frequent Visitor

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors