Filter on Filter

[iphoun]

First time poster and new to DAX. I'll try to explain this as best as I can. I have a Matrix visual that currently shows budget and actual performance data by phase/activity and the period selected. The budget and actual file are linked via a concatenated column that combines phase/activity to make it a unique field. I then have an independent Excel file which list phase/activity that contains an input field named "Unit Adj." that I would like to bring over the value when the visuals are filtered by phase/activity but instead of using the "Period" slicer, I would like to substitute that by allowing users to select a different time period filter called "Fcst Date". I'm able to verify that the sum of the phase/activity/fact table date does add up to 140.73 but how do I go about changing the expression of the visual column "UniAdj" so that I can bring in the value of the month that I select from the slicer "Fcst Date" instead of using the date from the fact table? What is the optimal relationship setup and dax formula to accomplish this? Thanks

VisualsVariable input file for forecasting purposeModel Relationship

[Anonymous]

Well, I would probably want to look at this but the description is not clear to me at all. On top of that, may I ask why you're having bi-dir filters everywhere? This is AGAINST Best Practices of data modeling in PBI.

Please refer to this first: How to Get Your Question Answered Quickly - Microsoft Power BI Community

[iphoun]

Hi daxer, I appreciate your response. The reason why I have bidirectional connection was bc the dim_export performances table contains the budget values and the columns value kept repeating itself in the visuals for various phases and activity rows. I am open to your suggestion on how to properly link the tables. This is one of my weak spot. What I was hoping to accomplish is to have the dim/fact_Fcst Unit Adj table act as a forecast input sheet whereby users will input Unit_Adj value by month in the Excel file and the visuals would calculate Hrs_to_Comp and Total Proj Hrs field in the visuals using the input from the Unit_Adj field based on a month slicer selection (Unit Adj. Fcst Date). Keep in mind that each project can have the same phase and activity but when concatenated (project/phase/activity) it becomes a unqiue key field. This is the reason why I am concatenating those 3 fields so that I have a key field. The Period slicer should be independent from the Unit Adj. Fcst Date slicer as the Period slicer is used to control the bugeted and actual hours and qty while the Unit Adj. Fcst Date slicer is used to calculate the Hrs_to_Comp and Total Proj Hrs measure calculation field. The pbi file and screenshots are listed below for your reference. Appreciate any help or suggestion you can provide. I look forward to your reply. Thanks!

Labor Tracker