DAX to filter out records

[Anonymous]

Hi Experts,

I'm still new to Dax and need some assistance to resolve the following;

I have the following sample record set which is based on different status's. So one notification you can have upto 10 different status's and change no is unique for each. I'm trying to create a Dax statement which will give me  the total (CG team) value based on Maximum changeno for the status selected. In the following scenario i need INCO status to be picked up as the Max.  The calculation should happen for the entire table. Please advise how to create the Dax.

Image 1

Thank you.

Regards

[v-xicai]

Hi @Anonymous ,

You can create column like the formula below :

Column1 = var d=MAXX(TOPN(1,FILTER(Table1,Table1[notificationno]=EARLIER(Table1[notificationno])),Table1[changeno],DESC),Table1[changeno])

return CALCULATE(SUM(Table1[total (cg team)]),FILTER(ALLSELECTED(Table1),Table1[notificationno]=EARLIER(Table1[notificationno])&&Table1[changeno]=d))

Best Regards,

Amy

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

[Anonymous]

In this context cannot use the Earlier function as the pop up for selecting the relevant table column is disabled. The notification no having duplicates however change no to pick as the minimum. Any thoughts? @v-xicai 

[v-xicai]

Hi @Anonymous ,

>>In this context cannot use the Earlier function as the pop up for selecting the relevant table column is disabled. The notification no having duplicates however change no to pick as the minimum.

I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

[Anonymous]

In the above table, if i filter records based on Status like 'INCO', at present moment i'm getting two records. Based on ChangeNo column I need to get the minimum ( in this case its 3 ) and then calculate total CG team value (i.e. 2360). So, end result should be 1 row for each notification no.  When calculating total result for CG team it should give me the total summation for CG team.  @v-xicai 

[Anonymous · Accepted solution]

I have used the below solution

https://community.powerbi.com/t5/Desktop/Using-Dax-to-remove-duplicates-before-summing-up-a-column/t...

This issue is now closed. Thanks all for replies.

[Anonymous]

Thanks Amy, I will implement your proposed solution and will let you know the outcome. 

Kind regards

Las