cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Override the dashboard filter and assign it to another column

Hi Buddy,

I am trying to figure out the DAX query to override a the dashboard filter and assign it to another column

This is my dataset

 id openDate closeDate Status 1 2/1/2024 Open 2 2/2/2024 Open 3 2/3/2024 Open 4 2/1/2024 Open 5 2/2/2024 Open 6 2/1/2024 2/2/2024 Close 7 2/2/2024 2/3/2024 Close

My dashbord have a filter, it is based on the openDate, user can multiple select the date they want

What I need to do is to form a chart like this

My solution
1. create an additional field reportDate which based on both openDate / closeDate
2. overide the original DashboardFilter

3. assign the filter to the field reportDate

(I can't make it to work )

DashboardFilter = DAY(Sheet1[openDate]) & "/" & MONTH((Sheet1[openDate]))

OpenTicket = CALCULATE(count(Sheet1[Status]),FILTER(Sheet1,Sheet1[Status] = "Open"))

closeTicket =
VAR MaxDate = MAXX(ALLSELECTED(Sheet1[openDate]),[DashboardFilter])
var Mindate = MINX(ALLSELECTED(Sheet1[openDate]),[DashboardFilter])
RETURN
calculate( COUNT(Sheet1[id]),
REMOVEFILTERS(Sheet1[DashboardFilter]),
filter('Sheet1', 'Sheet1'[Status] = "Close"),
DATESBETWEEN('Sheet1'[reportDate] ,Mindate, MaxDate)
)

This chart work fine if no filter was applied.

If select 2/2, the expected behavious is showing 2 for open and 1 for close

but now look like my DAX query get some problem

1 ACCEPTED SOLUTION
Super User

Consider utilizing USERELATIONSHIP  between your calendar table and the two dates in your fact table.

3 REPLIES 3
Super User

Consider utilizing USERELATIONSHIP  between your calendar table and the two dates in your fact table.

Frequent Visitor

Relationship active / non-active may work, it is a model change I am trying to avoid.

I am thinking if it is possible to do it from DAX side.

Super User

TREATAS is another option

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors