How to Implement If statement with Group by column from other table/Salesforce object

[Anonymous]

Hi Team

I am trying to implement below logic in power BI using Dax functionality, but not able to implement.

Need help on this.

Here I need sum of CU_CEO_Counter column based on group by of Org_Master_customer_if(which is from other table or object)

[amitchandak]

@Anonymous , Not very clear.

Something like that

sumx(values(Table[Org_Master_customer_id]), if(isbalank(sum(CU_CEO_Counter)),1,0))

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

[Anonymous]

Amit,

I am not getting any option to attach power BI report and excel.

Can you please guide me?

[Anonymous]

Amit,

Thanks for your quick reply . Really appreciate!

Now I am getting data but I need to validate, as I am finding few rows showing more number. It should be mostly 1,2,3 like this. Seems very close, just need to change the code little bit.

Really confusing about Dax query.

Secondly, how can I send this file as very big file.

Can I send you with some sample example data in excel?

[amitchandak]

@Anonymous , Create a sample data and sample output and upload that to one drive or dropbox and share.

[Anonymous]

Amit,

Here is link for power BI pbix file

https://cuna-my.sharepoint.com/:u:/g/personal/bswain_cuna_coop/Efz3IVLFuA9HoURyNCeiq9ABqNVtFkWbySLjK...

Excel:- Data set and how the output should look like.

I want the result set look like H column in Result should be sheet.

Existing formula's screen shot i have put beside H column

As per the formula screen shot mentioned beside H column

https://cuna-my.sharepoint.com/:x:/r/personal/bswain_cuna_coop/_layouts/15/Doc.aspx?sourcedoc=%7B007...

[v-deddai1-msft]

Hi @Anonymous ,

Would you please try the following measure:

No Protection Count = IF(MAXX(SUMMARIZE('Bank Detail','Bank Detail'[Org Master Id]," Protection count",SUM('Product'[Company With Protection count])),[ Protection count]) = 0,1,0)

Is the screenshot below you want?

If it's not, please show us more details about your expected output.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi Dedmon,

Thanks for replying!

The mentioned query is working for partly. This is really query i am using for my project.

Bidya = IF(MAXX(SUMMARIZE('Account','Account'[Cuna ID]," CU CEO Counter2",

SUM('Account Child'[CU CEO Counter2])),'Account Child'[CU CEO Counter2]) = 0,1,0)

1.here if the value for CU CEO counter2 is 0 then it is working

2. but if it is either 0 or 1 mix for a particular cuna id it is not working

so what i am thinking if we can add another logic that if cu CEO counter is 1 or 0

then it should be 0,then that should be fine.

So let me know how can i add that logic?

so basically in summary the logic should be:-

if CU CEO counter=0 for a specific id then result should be 1(now this logic is working perfectly)

else if CU CEO counter= mix of 0 or 1 then it should be 0

[v-deddai1-msft]

Hi @Anonymous ,

There is something wrong with your query. Would you please use the following measure instead:

Bidya =
IF (
    MAXX (
        SUMMARIZE (
            'Account',
            'Account'[Cuna ID],
            " CU CEO Counter", SUM ( 'Account Child'[CU CEO Counter2] )
        ),
        [CU CEO Counter]
    ) = 0,
    1,
    0
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi Dedmon,

Good thing is i am able to achive this using one very simple formula but it is working for part condition and other one if then we have to add.

my current logic:-

CU No CEO2 = IF(CALCULATE(SUM('Account Child'[CU CEO Counter2]),'Account'[Cuna ID])=0,1,0)

If you will see my screen shot.

1.where all CU CEO Counter2 is 0 for specific id( below pics) the CU NO CEO2 is showing correctly as 1

2. But where CU CEO Counter2 for another id is mix of 1 and 0 is not showing as 0,in fact that is correct as per my below formula.

so in order to achive second condition we need to add multiple if then else condition with existing group by condition like below:-

If CU CEO Counter2 is 0 then 1

if CU CEO Counter2 is 1  then 0

or else if it is 1 or 0 then 0

[v-deddai1-msft]

Hi @Anonymous ,

If I can understand your requirements well, I can help you well. Are you trying to achieve below logic for your Parent Name:

If all CU CEO Counter2 in Parent Name is 0 then1

If all CU CEO Counter2 in Parent Name is 1 then 0

If all CU CEO Counter2 in Parent Name is mixed of 0 and 1 then 1

Use the below measure :

Bidya =
IF (
    MAXX (
        ADDCOLUMS(SUMMARIZE (
            'Account',
            'Account'[Cuna ID],
           ), " CU CEO Counter", CALCULATE (AVERAGE ( 'Account Child'[CU CEO Counter2] ))
        [CU CEO Counter]
    ) < 1,
    1,
    0
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Dedmon,

Here i am attaching my dashboard link which is having grouping issue.

Here i have selected two company,American First CU  and Alliant CU,

so for American First CU  for CU CEO Counter one record is showing 1 and all other are 0,so CU No CEO2 should be 0 for all row but here it is not working that way.Let me know if any other info you need from me.

Basically here i am using parent and child relationship.

Appreciate your help!

https://1drv.ms/u/s!AlADAlckCzipfgY4nSMMVhPaO-s

[v-deddai1-msft]

Hi @Anonymous ,

I can't understand your data model. There are two same table. But you create a relationship between with different columns. And I really don't understand your logic.

Best Regards,

Dedmon Dai

[Anonymous]

Dedmon,

 I have resolve the issue. Thanks for your continous effort to help me.

Regards

Bidys

[Anonymous]

When I created this measure in report view below account child table and put in report why it is taking lot of time and not coming? loading for long time. error came not enough memory to complete the operation

Actually I am using below model. where i am building hierachical relation between account and child account and both are same table alias

so i am using my column in this way. 

Account Table(parent table)

Name - as company name

ID- Company ID

Account Table(child table)

Name- Employee name

id- Employee id

co counter -calculation from child table

I am not understaning where is the issue