Calculate function with filters from 2 tables resulting blank

[Anonymous]

Hi guys how do you filter from 2 tables using the calculation function? 

Is the syntax below written properly?

Test = 

CALCULATE(SUM(IB_OB_Data[Accepted]),FILTER(ALL(Calendar_Data),Calendar_Data[Week No] = SELECTEDVALUE(Calendar_Data[Week No])-1),FILTER(IB_OB_Data,IB_OB_Data[Interaction Type] = "Outbound"))

Thank you

[Anonymous · Accepted solution]

Hi @Anonymous ,

Do you want to get the sum of Accepted for the previous week of the selected week? If yes, I created a sample pbix file(see attachment), please check whether that is what you want. You can create a measure as below:

Test = 
VAR _selweek =
    SELECTEDVALUE ( 'Calendar_Data'[Week No] )
VAR _preweek =
    CALCULATE (
        MAX ( 'Calendar_Data'[Week No] ),
        FILTER ( ALL ( 'Calendar_Data' ), 'Calendar_Data'[Week No] = _selweek - 1 )
    )
RETURN
    CALCULATE (
        SUM ( 'IB_OB_Data'[Accepted] ),
        FILTER ( 'IB_OB_Data', WEEKNUM ( 'IB_OB_Data'[Date] ) = _preweek )
    )

If the above one is not your expected result, please provide some sample data with Text format and your expected result with backend logic and some special examples. Thank you.

Best Regards

[amitchandak]

@Anonymous , Seem fine, check there is no filter on Interaction Type. May be Interaction Type <> "Outbound" is already in filter

Try this version of measure

Test =
var _max = maxx(allselected(Calendar_Data) ,Calendar_Data[Week No])
return
CALCULATE(SUM(IB_OB_Data[Accepted]),FILTER(ALL(Calendar_Data),Calendar_Data[Week No] = _max-1),FILTER(IB_OB_Data,IB_OB_Data[Interaction Type] = "Outbound"))

If this does not help

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.