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
Anonymous
Not applicable

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. 

1.png

 

 

 

 

 

 

 

 

And I design a table to display using the mapped field as row and measure as value. CC , H2 , H4 (all from mapping table for using using same slicer to filter Cost measure and Budget Measrue in one combined table.) as the row and budget as the value. 

But the wired thing is , when I tried to display the budget in the table , it only displays the catogory which is existing in Cost table. 

For Example,

1.png

The total is correct. But the category list missed the ones which have no cost this year. And it should have budget. Total 528 , and the total of displayed category is 500 , 28 missed is belonging to the missed one category. 

 

Here is my DAX of measure, 

B Y =
VAR LastDateFromFact = MAX( FBL3N[date] )
RETURN
CALCULATE(
Budget[Budget],
'Calendar'[year]=YEAR(LastDateFromFact)
)
 
And after combine with the cost , the table is as below. 
1.png
The highlighted field is the missing one (28). 
 
Do anyone know how I can change anything to display all budget value even it's no cost happen? 
 
Thanks!
4 REPLIES 4
Anonymous
Not applicable

I find the cause of this issue. When I delete the below code in DAX, and set the calendar date as a fixed date. It works. All the values appeared. 

VAR LastDateFromFact = MAX( FBL3N[date] )
RETURN
 
But we require the dynamic value to define the last update date value in the data source. So it can't be removed. 
Do anyone know why the dynamic value will influence the total report? And how I can use this dynamic value and not impact the report? 
 
Thanks!

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. 

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-xuding-msft , 

 

Hope you can help on this case. I'm really stuck by this point.

 

Thanks!

Anonymous
Not applicable

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. 

1.png

 

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.

2.png

 

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!

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.