Forum Discussion
DAX filter across 2 tables
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.
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
ITDTotalIf 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
4 Replies
- amitchandak
Super User
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
ITDTotalIf 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
ITDTotalI 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
Community Champion
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.Hi Greg
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 300Above 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 = 600However, 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
