DAX Filter A Table by Another Table

[numersoz]

Hi,

I have a table where I need to average out time stamped values. I want to filter this table by the product ID's contained in another table. Both the time stampped table and the other table contraining product ID's are related using a product lookup table.

I've treid to use CALCULATE, but I keep getting errors. I know I can make this work if I type in all the product ID's manually, but obiously this is not practical. 

Any tips?

[numersoz]

@Greg_Deckler @pranit828 

Hi,

Measure = CALCULATE(AVERAGE('Data Hourly'[Amount]), EXCEPT(VALUES('Data Hourly'[productName]), VALUES('Target'[productID])))

My tables are as such:

• Data Hourly:
  • timeStamp
  • productName
  • Amount
• Target:
  • productName
  • productID
  • target
• productTable:
  • productName
  • productID

Data Hourly has one to many relationship with productTable using productName.

productTable has one to one relationship with Target using productID.

I want to plot amount and target, however as target table does not have all of the products, I want to plot only for the product's that exists in the target table. The problem I have is, the plot of amount is also showing products that are not part of the target table.

[amitchandak · Accepted solution]

@numersoz , Try like

calculate(sum(table1[Values]),filter(Table1, Table1[Product ID] in Values(Table2[Product ID])))
calculate(sum(table1[Values]),filter(Table1, Table1[Product ID] in allselected(Table2[Product ID])))

or try treatas

https://docs.microsoft.com/en-us/dax/treatas-function

[numersoz]

@amitchandak 

Thank you! This worked.

[pranit828]

Hi @numersoz 

Please post sample i/p  and o/p data.

My hunch is that it is due to the way your table is joined. either one to many or many to many.

[Greg_Deckler]

@numersoz - Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.