Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    I got it figured out! The All() was messing with the relationship, but once removed it was fixed.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous 

 

Are you calculating Trend based on different classifications? What is the basis for the calculation? Could you please share some sample data and expected results based on sample data? So that I can help you better. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account. Thank you for your time and efforts in advance.

 

Best Regards,
Yulia Xu

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I got it figured out! The All() was messing with the relationship, but once removed it was fixed.