Forum Discussion
Table to Measure Relationship Help
Hi! I have created the following measure, which calculates correctly ( I have created the measure in two ways: with variables and without). But the relationship isnt picking it up it seems to be a...
I got it figured out! The All() was messing with the relationship, but once removed it was fixed.
Hi,
I cannot post the pbix, but I can post the measures and screenshots.
The measure formula that works correctly is:
CALCULATE(SUM('Sales Data'[sales_price]), FILTER('Sales Data','Sales Data'[Month Match] = true && 'Sales Data'[Month if Matching] = [Current Month]-12))/ CALCULATE([# of WorkDays], FILTER('Date Key', [Current Month]-12))
The measure formula I need to use due to some date constraints is:
WORK12MONTH =
VAR __Today = TODAY()
VAR __Max = EOMONTH( __Today, -12 )
VAR __Min = DATE( YEAR( EOMONTH( __Today, -12) ), MONTH( EOMONTH( __Today, -12 )), 1 )
VAR __Table = FILTER( ALL( 'Date Dimension'), 'Date Dimension'[FullDateAlternateKey] >= __Min && 'Date Dimension'[FullDateAlternateKey] <= __Max )
VAR __Result = SUMX(__table, [# of Work Days])
RETURN
__Result
The first formula works and allows the breakup between branches to show the trrend per branch the new measure does not.
The relationships are the same and use the same variables. So I am not sure why it wont work.
I got it figured out! The All() was messing with the relationship, but once removed it was fixed.