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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GVTioluxe
Helper I
Helper I

Slicer with inactive and active relationships

Hi

 

I have a Sales lead table which has 2 date fields - the Lead acquired Date and Last engagement Date. There is an active relationship of the Lead Acquired Date to the Calendar Table and an inactive relationship to the engagement date.

 

I would like to have 2 table visuals - one showing the customer names with lead acquired date and the second custome with engagement date.

 

i would like a date slicer so when i select a particular month, it will display the customers with lead date for that month in the first visual and customers with engagement date during that month in the second visual

 

can someone advise how to do this?

 

regards

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @GVTioluxe ,

 

Inactive the relationships and create two slicers as below.

measure 1 = IF(SELECTEDVALUE('SALES'[Lead acquired Date])=SELECTEDVALUE('CALENDAR'[DATE]),1,0)

measure 2 = IF(SELECTEDVALUE('SALES'[Last engagement Date])=SELECTEDVALUE('CALENDAR'[DATE]),1,0)

Then add these measures to visual filter measure = 1.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

Hi @GVTioluxe ,

 

Inactive the relationships and create two slicers as below.

measure 1 = IF(SELECTEDVALUE('SALES'[Lead acquired Date])=SELECTEDVALUE('CALENDAR'[DATE]),1,0)

measure 2 = IF(SELECTEDVALUE('SALES'[Last engagement Date])=SELECTEDVALUE('CALENDAR'[DATE]),1,0)

Then add these measures to visual filter measure = 1.

 

Best Regards,

Jay

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

@GVTioluxe , I case you want them to run in the same month, then you can use a common date table. Use userelation to choose Date in a measure

refer example

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

In case you want both of them to have independent dates , then you use those dates or need to have to tables.

Hi Amit

 

thanks for your response. I have been able to create the DAX to count the numbers for both the dates, but my problem is when i want to list the customers in the table visual, it filters based on the active relationship.

 

are u suggesting to keep both inactive and in both the DAX use the USERELATIONSHIP function to get the count and with this i can get the correct listing of customers?

 

rgds

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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