Filter with DAX

[Anonymous]

Good morning all !

I ask for your help because I have a problem in my Power BI.

I have two tables:

• A "Master table" where I have 4 columns:
  • Date of extraction
  • ABC code
  • MaterialCode
  • Forecast

• my second table is called "DAD_Factory" and I have 5 columns:
  • Date of extraction
  • MaterialCode
  • ADI Number
  • ReasonPV
  • Stock

What I want to do is create 3 columns in DAX (or a measure) which will display me

• the "ADI Number"
• The "reasonPv"
• The "Stock"

which is in the column "DAD FACTORY"

What I want to do with this report is a kind of historical follow-up, that is to say that when I filter on the date of the extraction with a filter pane, I want that in my master Table I can have my "MaterialCode", "my" Forecast "with my 3 new columns that I would have created

For example :

On January 1st, 2021 my "MaterialCode": 71673 had a forecast of 7K, a stock of 10k, it had 3 ADI Number "381/25624352"/ "385/25624352"/ "388/25624352" and the reason for these PVs was "human problem"/"Failure to respect the order"/ "late delivery".

I'm not sure if my request is feasible, but I would need your help!

I just want to clarify something too:

A MaterialCode can have more than one ADI code and more than one PV.

I tried to create a relation between the two tables with the "MaterialCode" as primary key (see below my relation that I used)

the problem is that when I want to filter by date code of the extraction, nothing filters correctly

I hope you can help me !!

Thanks

[lbendlin · Accepted solution]

Think about improving your data model. Right now you have two fact tables. You will want to add some dimension tables like the Material Dimension and Calendar Dimension etc.