Forum Discussion

MarcusUK's avatar
MarcusUK
New Member
5 years ago
Solved

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...
  • amitchandak's avatar
    amitchandak
    5 years ago

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
MarcusUK
New Member
5 years ago

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.