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 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,

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. 
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!
Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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!
    • 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!