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
pmcmonag
Advocate IV
Advocate IV

CALCULATE > FILTER > USERELATIONSHIP combination for secondary date field not working

I'm struggling to find a working combination of filters and then using USERELATIONSHIP to return values based on a different date field. Dummy example below and file too. 

 

I'm trying to use a combination of filters to return (1) successful 'pitches' over £500K+ (2) that return based on a secondary date field. 

(1) is fine just using two FILTER functions in the 'Successful over £500K+' measure. 

(2) on it's own works in the 'Value by Confirmed Date' measure - honouring the intial filter context from the slicer. 

 

Putting these into one measure, as in the screenshot produces blanks, rather than where expected. 

 

Annotation 2020-02-05 145347.png

 
 
 

Example File 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi
Try to use like this : 

 

Calculate ( your function with filters ) ; userelationship()
So u just put your measure inside another Calculate measure  :    

example : CALCULATE(
CALCULATE(SUM(BASE[ MARGE ACT]);FILTER(BASE;BASE[ MARGE ACT]<0);USERELATIONSHIP('Date'[Date];BASE[Date])

HOWEVER as i see u are linking two differents tables in your filters (1 and 2)
so for this i would try this measure(but there should be a relationship between two tables) : 
example : CALCULATE(
CALCULATE(SUM(BASE[ MARGE ACT]);FILTER(BASE;BASE[ MARGE ACT])<0;FILTER(RELATEDTABLE('Paramètre');'Paramètre'[Paramètre]<0);
USERELATIONSHIP('Date'[Date];BASE[Date])


Hope its clear

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi
Try to use like this : 

 

Calculate ( your function with filters ) ; userelationship()
So u just put your measure inside another Calculate measure  :    

example : CALCULATE(
CALCULATE(SUM(BASE[ MARGE ACT]);FILTER(BASE;BASE[ MARGE ACT]<0);USERELATIONSHIP('Date'[Date];BASE[Date])

HOWEVER as i see u are linking two differents tables in your filters (1 and 2)
so for this i would try this measure(but there should be a relationship between two tables) : 
example : CALCULATE(
CALCULATE(SUM(BASE[ MARGE ACT]);FILTER(BASE;BASE[ MARGE ACT])<0;FILTER(RELATEDTABLE('Paramètre');'Paramètre'[Paramètre]<0);
USERELATIONSHIP('Date'[Date];BASE[Date])


Hope its clear

Thanks this worked! I'm not entirely sure why, I guess something to do with context/ context transition!? I just needed the extra 'CALCULATE' wrap around as the tables were already related. 

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.

Top Solution Authors