topic Re: If statement using Custom Column in DAX Commands and Tips

If statement using Custom Column

Anonymous — Mon, 19 Apr 2021 09:20:47 GMT

Hi Team

im trying to get the logic of how i would make this work so that it can dispay everything not just the first command under Custom column, Please help

if[AffiliateOwnerID]=2 and [ActivityTypeID]=1 or[ActivityTypeID]=2 then 2
else if [AffiliateOwnerID]=2 and [ActivityTypeID]=1 or[ActivityTypeID]=2 then 3
else if [AffiliateOwnerID]=2 and [ActivityTypeID]=1 or[ActivityTypeID]=2 then 5

Reason why we need 3 targets under one activity is because we wat to asign each target to a certain percentage like on the screen shot below

Re: If statement using Custom Column

Payeras_BI — Mon, 19 Apr 2021 09:35:55 GMT

Hi Anonymous ,

Aren't all the if-conditions the same here?

Re: If statement using Custom Column

Anonymous — Mon, 19 Apr 2021 09:54:48 GMT

Hi Payeras_BI

if you check, we need to have a target of 2,3 and 5 for the same activities ID under the same affiliate owner.

so the logic is that we want all those targets to show on that column so we can calculate the SLA % to indcate that

2=70%

3=85%

5=95%

i hope this will make it easy for you to understand the logic

Re: If statement using Custom Column

Payeras_BI — Mon, 19 Apr 2021 11:10:03 GMT

Hi Anonymous ,

Not sure if this is what you are looking for but try with this instead to get an idea of why the previous code was not working and take it from there.

= if[AffiliateOwnerID]=2 and ([ActivityTypeID]=1 or[ActivityTypeID]=2) then 2 else if [AffiliateOwnerID]=3 and ([ActivityTypeID]=1 or[ActivityTypeID]=2) then 3 else if [AffiliateOwnerID]=5 and ([ActivityTypeID]=1 or[ActivityTypeID]=2) then 5 else null

Re: If statement using Custom Column

Anonymous — Mon, 19 Apr 2021 12:11:59 GMT

Hi Payeras_BI it is for the same affilate owner that have affiliate owner 2, it just have 3 different targets. Do you perhaps have an idea of how we can achieve that

Re: If statement using Custom Column

Payeras_BI — Mon, 19 Apr 2021 14:03:51 GMT

Hi again Anonymous ,

Let's see if I understood it now.

You start with something like this:

And want to end up with something like this:

If so, here is my proposal:

1. Create an Aux table like this one (named Target in my example):

2. Add a Custom Column to your main table with the following code:

Table.SelectRows(Target, (X)=> List.Contains({1,2},[ActivityTypeID]) and [AffiliateOwnerID] = 2)

3. Expand it: