DAX filter across 2 tables

[MarcusUK]

Hello everyone, I really need someone's help here as I am struggling with thsi DAX formula. I have these tables below:

Data table called "data - phasing"

Period table called "xMeasures - Periods"

CBS Code table called "ref - CBS Table"

I also have this current DAX formula which works fine fine at the moment and returns the ITD VOWD for the Current Forecast that is coming from the data - phasing tab.

I now need to also filter on "Non Progress" but this field is not in the data - phasing table, the field is in the ref - CBS Table and is linked to the CBS Code, which is in both the data - phasing and ref - CBS Table. All tables are linked to the ref - CBS Table

This is the DAX I have right now which works well but I cant work out how to add the additional filter to include Non Progress only

I have this DAX formula below which works perfect at the moment. However, I now want to filter another value from a different table. 

VOWD ITD C - Non Progress =

VAR CBS = "Non Progress"

VAR CAT = "Current Forecast"

VAR StartDate =

DATE ( 1900, 1, 1 )

VAR EndDate =

DATE ( YEAR('xMeasures - Periods'[Period Current]),MONTH('xMeasures - Periods'[Period Current]), 1 )

VAR ITDTotal =

CALCULATE(SUM('Data - Phasing'[Value]),

DATESBETWEEN ('Data - Phasing'[Period], StartDate, EndDate ),

FILTER('Data - Phasing','Data - Phasing'[Description] = CAT)

)

RETURN

ITDTotal

[amitchandak · Accepted solution]

@MarcusUK , If they are connected tables

VOWD ITD C - Non Progress =
VAR CBS = "Non Progress"
VAR CAT = "Current Forecast"
VAR StartDate =
DATE ( 1900, 1, 1 )
VAR EndDate =
DATE ( YEAR('xMeasures - Periods'[Period Current]),MONTH('xMeasures - Periods'[Period Current]), 1 )
VAR ITDTotal =
CALCULATE(SUM('Data - Phasing'[Value]),
DATESBETWEEN ('Data - Phasing'[Period], StartDate, EndDate ),
FILTER('Data - Phasing','Data - Phasing'[Description] = CAT)
FILTER('CBS Table','CBS Table'[Non Progress / Progress] = CBS)
)
RETURN
ITDTotal

If they are not connected 
VOWD ITD C - Non Progress =
VAR CBS = summarize(filter('CBS Table','CBS Table'[Non Progress / Progress] ="Non Progress"),[CBS Code])
VAR CAT = "Current Forecast"
VAR StartDate =
DATE ( 1900, 1, 1 )
VAR EndDate =
DATE ( YEAR('xMeasures - Periods'[Period Current]),MONTH('xMeasures - Periods'[Period Current]), 1 )
VAR ITDTotal =
CALCULATE(SUM('Data - Phasing'[Value]),
DATESBETWEEN ('Data - Phasing'[Period], StartDate, EndDate ),
FILTER('Data - Phasing','Data - Phasing'[Description] = CAT && 'Data - Phasing'[CBS Code] in CBS )
)
RETURN
ITDTotal

[MarcusUK]

@amitchandak 

I must have been really tired yesterday or was missing something very silly. I tried both those solutions yesterday and none of them worked and today it did 🙂

Thank you so much for your help.

[Greg_Deckler]

@MarcusUK Sorry, having trouble following, can you post sample data as text and expected output?
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.

[MarcusUK]

Hi Greg

@Greg_Deckler

Thanks for getting back to me so soon, I hope I have explined this clearly enough 🙂

Greg I am getting this error when trying to send the message

Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied.

I had to remove the tables,

Ref - CBS Table
CBS Code         Non Progress / Progress
1100                 Progress
1200                 Non Progess
1300                 Non Progress
1400                 Progress
1500                 Non Progress

Data - Phasing
CBS Code      Period        Description            Amount
1100              01/01/20   Current Forecast    100
1100              01/01/20   Previous Forecast   200
1200              01/10/21   Current Forecast    300
1200              01/10/21   Previous Forecast   600
1300              01/08/20   Current Forecast    200
1300              01/08/20   Previous Forecast   400
1400              01/05/20   Current Forecast     300
1400              01/05/20   Previous Forecast    300

Above is the main data which I am trying to filter from table Data - Phasing. There is another Period table but too much to include here but basically the Var start date is right from the beginning and the Var end date which returns the 31/12/20, this is working fine right now 

Currently the DAX formula will return the below:
a) Up to 31/12/20
b) Current Forecast ONLY
This returned a value of = 600

However, I now need to add an additional filter, filtering data referencing the Ref - CBS Table and only calculate the Non Progress CBS Codes (these are mapped / linked to each other). This would then give me a result of = 200

How can I add one more filter that filters out the Non Progress CBS codes that are linked to the CBS codes in the Data - Phasing table?

Thanks Greg