Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Value missing in the Matrix

Hi ,    I have two source data table , Cost and Budget. And several mapping table related to this two source data as 1 to many.                  And I design a table to display using...
Need Help
v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

Hi Anonymous ,

Due to the multiple tables in your sample, I can't make the problem clear. Can you please share some sample data and expected results? If okay, it is better to share a dummy pbix file that we can test. 

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi v-xuding-msft , 

 

I'm not sure if I can make an example. It's so complicated. 

 

As I mentioned , I have two source table , cost and budget , and several mapping table link to the two sources tables with one to many relationship. What I expect is to mix the measurement from this two tables to one table by using same row level. 

 

For example , when the measure is below. (same for Act Y-2 , Y-3) . FBL3N is the source cost table. 

Act Y-1 =
VAR LastDateFromFact = MAX( FBL3N[date] )
RETURN
CALCULATE(
FBL3N[Amount],
'Calendar'[year]=YEAR(LastDateFromFact)-1
)
 
The value looks like below. You can see the total is 1634, but the sum of displayed value is not exactly 1634. there are some value missed. 

 

But when the measure is 

Act Y-1 =
VAR LastDateFromFact = DATE(2020,1,1)
RETURN
CALCULATE(
FBL3N[Amount],
'Calendar'[year]=YEAR(LastDateFromFact)-1
)

 

The value looks like below. all the value appear correctly.

 

VAR LastDateFromFact = MAX( FBL3N[date] ) is to get the lastest updated date in cost table. then we can know what's the current year or current date. 

But seems if I use the max function , the calculation will run , but it will decide not to display if there is no value for the specific row level. 

 

And we can't use now() to get the date because we still need to generate Dec report when it's Jan of next year. 

 

Hope i have clarify the issue clearly. 

 

Thanks!