Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Bellringing
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

idopenDatecloseDateStatus
12/1/2024 Open
22/2/2024 Open
32/3/2024 Open
42/1/2024 Open
52/2/2024 Open
62/1/20242/2/2024Close
72/2/20242/3/2024Close

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

Bellringing_2-1707434776289.png

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

Bellringing_1-1707434143221.png

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. 

Bellringing_1-1707434143221.png

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

Bellringing_4-1707435538348.png

but now look like my DAX query get some problem

Bellringing_3-1707435491657.png

pbix file link 

https://drive.google.com/file/d/16XS7Gmjy-Wx167-Iq1BUCGsXRjVD55xy/view?usp=sharing

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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

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. 

TREATAS is another option 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors