Need Help with IN operator

DAX DAX Calculations
1

Hi guys,
most probably I shouldn’t use In operator for what I need to do.
Suppose we run campaigns and each campaign gets an id. So for a selected campaign I want to find the customers that had also purchased a policy through a previous campaign that they canceled itin order to buy from the new campaign. For those customers I want to calculate the amount cancelled (surrender amount) from the previous campaign

The dax measure looks like this. I am stuck in the last line of code … I know I cant use IN operator like this … I need an equivalent syntax…

Surrender Amount w/ Customer check = 
    VAR SelectedCampaignID = SELECTEDVALUE( 'Campaigns Master'[CampaignID] )
    VAR SelectedCampaignStartDate = SELECTEDVALUE( 'Campaigns Master'[CampaignStartDate] )
    VAR SelectedCampaignEndDate = SELECTEDVALUE( 'Campaigns Master'[CampaignEndDate] )
    VAR InitialSet = CALCULATETABLE( VALUES( Policies[CustomerCode] );
                             FILTER( ALL( 'Campaigns Master' ) ;
                                 'Campaigns Master'[CampaignID] = SelectedCampaignID ) )    
RETURN
    CALCULATE( [Surrender Amount]; 
         Surrenders[SurrenderDate] >= SelectedCampaignStartDate &&
         Surrenders[SurrenderDate] <= SelectedCampaignEndDate;
         Surrenders[CampaignID] < SelectedCampaignID;
         **Surrenders[CustomerCode] in {InitialSet}** )

Thanks in advance
George

2

@Cavaliotis,

It’s difficult to provide a fully specific solution without a PBIX file to work with, but I get the gist of what you’re trying to do. I think the video below will help you get to a solution, but if not, please post a PBIX and I’d be glad to work with you on the specific DAX revisions needed.

Hope this is helpful.

  • Brian
3

Thanks Brian … I will get to it and let you know

George

4

Brian
it did’t really help. What is presented in the video I already do it in calcs like the following

Dual Participation (Surrendered) w/ canc date Check = 
    VAR SelectedCampaignID = SELECTEDVALUE( 'Campaigns Master'[CampaignID] )
    VAR SelectedCampaignStartDate = SELECTEDVALUE( 'Campaigns Master'[CampaignStartDate] )
    VAR SelectedCampaignEndDate = SELECTEDVALUE( 'Campaigns Master'[CampaignEndDate] )
    VAR InitialSet = CALCULATETABLE( VALUES( Policies[CustomerCode] );
                             FILTER( ALL( 'Campaigns Master' ) ;
                                 'Campaigns Master'[CampaignID] = SelectedCampaignID ) )
    VAR ComparisonSet = CALCULATETABLE( VALUES( Policies[CustomerCode] );
                             FILTER( ALL( 'Campaigns Master' ) ;
                                 'Campaigns Master'[CampaignID] < SelectedCampaignID );
                             FILTER( ALL( Policies ) ;
                                Policies[PolicyStatus] = "Canceled");
                             FILTER( ALL( Policies ) ;
                                Policies[PolicyCancelationDate] >= SelectedCampaignStartDate && Policies[PolicyCancelationDate] <= SelectedCampaignEndDate );
                            TREATAS( VALUES( 'Comparison Campaigns'[Campaign] ); 'Campaigns Master'[Campaign] )
                        )
RETURN
    IF( SELECTEDVALUE( 'Campaigns Master'[Campaign] ) = SELECTEDVALUE( 'Comparison Campaigns'[Campaign] );
        BLANK();
        COUNTROWS( INTERSECT( InitialSet; ComparisonSet ) )
)

I can understand that … I am creating virtual single column tables and then I use intersect to count rows …

The case with the culculation I have trouble is that I want to sum another measure based on an intersection …
unfortunnately I cannot upload the file … it contains sensitive organizational data …
I will look at it tomorrow with clear head … I have been working 11 hours already…
If I dont find a solution I will create demo pbix and upload it …

Thanks for your response anyway.
best regards
Gerrge

5

@Cavaliotis,

Sounds like a plan. FYI - I have done a video that should drop late today on the Enterprise DNA TV YouTube channel on some quick and easy techniques you can use to anonymize your data/remove sensitive information to allow you to post it on the forum.

  • Brian
6

Thanks Brian …

7

Hi Brian,
after all I got it working … it was just simple …
here is the calculation …

Surrender Amount w/ Customer check =
VAR SelectedCampaignID = SELECTEDVALUE( ‘Campaigns Master’[CampaignID] )
VAR SelectedCampaignStartDate = SELECTEDVALUE( ‘Campaigns Master’[CampaignStartDate] )
VAR SelectedCampaignEndDate = SELECTEDVALUE( ‘Campaigns Master’[CampaignEndDate] )
VAR InitialSet = CALCULATETABLE( VALUES( Policies[CustomerCode] );
FILTER( ALL( ‘Campaigns Master’ ) ;
‘Campaigns Master’[CampaignID] = SelectedCampaignID ) )
VAR SurrenderedSet = CALCULATETABLE( VALUES( Surrenders[CustomerCode] );
‘Comparison Campaigns’[CampaignID] < SelectedCampaignID ;
Surrenders[SurrenderDate] >= SelectedCampaignStartDate &&
Surrenders[SurrenderDate] <= SelectedCampaignEndDate
)
RETURN
IF( SELECTEDVALUE( ‘Campaigns Master’[Campaign] ) = SELECTEDVALUE( ‘Comparison Campaigns’[Campaign] );
BLANK();
CALCULATE( [Surrender Amount];
INTERSECT( InitialSet; SurrenderedSet ) ) )

8

Just filtered the calculation with the result of the Intersect…

Best regards and thanks for your time
George