cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Dynamic Filtering using slicer

I have a table with data below. I have a date slicer which is filtering the date in this table. Based on the date range selected I need to filter my table in such a way that for each company
and type M it brings the smallest and largest date and corresponding amount and excludes other type M records. Rest all the type records(X,Y) should appear as it is.
Basically 2 records of type M for each company and the first record should have the amount negative.I want to create a calculated table based on this.Is it achievable in DAX?

Input table

 group company_name Type Date Amount AA A X 28-02-2019 100 AA A Y 27-03-2019 200 AA A M 27-04-2019 300 AA B X 10-05-2019 400 AA A M 29-04-2019 500 AA A M 15-05-2019 500 AA B M 10-06-2019 600 AA B M 11-06-2019 700 AA B M 20-06-2019 800

Output Table:

 group company_name Type Date Amount AA A X 28-02-2019 100 AA A Y 27-03-2019 200 AA A M 27-04-2019 -300 AA B X 10-05-2019 400 AA A M 15-05-2019 500 AA B M 10-06-2019 -600 AA B M 20-06-2019 800

Thank you!

1 ACCEPTED SOLUTION
Community Support

Hello @valentina14

you could use this formula to create a new table

``````New Table =
FILTER('Table','Table'[Type]<>"M"||
('Table'[Type]="M"&&
('Table'[Date]=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[group]=EARLIER('Table'[group])&&'Table'[company_name]=EARLIER('Table'[company_name])&&'Table'[Type]="M"))||
'Table'[Date]=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[group]=EARLIER('Table'[group])&&'Table'[company_name]=EARLIER('Table'[company_name])&&'Table'[Type]="M")))))``````

Result:

and here's the sample pbix file, please try it out.

Best regards

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support

Hello @valentina14

you could use this formula to create a new table

``````New Table =
FILTER('Table','Table'[Type]<>"M"||
('Table'[Type]="M"&&
('Table'[Date]=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[group]=EARLIER('Table'[group])&&'Table'[company_name]=EARLIER('Table'[company_name])&&'Table'[Type]="M"))||
'Table'[Date]=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[group]=EARLIER('Table'[group])&&'Table'[company_name]=EARLIER('Table'[company_name])&&'Table'[Type]="M")))))``````

Result:

and here's the sample pbix file, please try it out.

Best regards

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

@Anonymous , Add these two measure in place of date and amount in visual

firstnonblank(Table[Date], blank())
firstnonblankvalue(Table[Date], max(Table[Amount]))

And check

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.