topic Re: DAX filter across 2 tables in DAX Commands and Tips

DAX filter across 2 tables

MarcusUK — Tue, 29 Dec 2020 16:29:49 GMT

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

Re: DAX filter across 2 tables

Greg_Deckler — Tue, 29 Dec 2020 17:17:49 GMT

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.

Re: DAX filter across 2 tables

MarcusUK — Tue, 29 Dec 2020 18:44:19 GMT

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

Re: DAX filter across 2 tables

amitchandak — Wed, 30 Dec 2020 04:28:30 GMT

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

Re: DAX filter across 2 tables

MarcusUK — Wed, 30 Dec 2020 05:16:10 GMT

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.