cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

DAX to filter out records

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 1Image 1

 

 

 

 

Thank you.

Regards

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

6 REPLIES 6
v-xicai
Community Support
Community Support

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
Not applicable

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
Community Support
Community Support

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
Not applicable

Capture2.PNG

 

 

 

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
Not applicable

Anonymous
Not applicable

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

Kind regards

Las

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors