Forum Discussion

_Regina's avatar
_Regina
Icon for Helper I rankHelper I
3 years ago
Solved

Matrix table gives correct result based on one column but wrong based on other column of same table

 

Table 1
ID
Name

 

Table 2
Date
ID
DollarValue

 

These tables are related via ID column- one to many.

When I create a matrix visual using Name from Table 1 and DollarValue from Table 2, it works fine. But when I create matrix visual using ID from Table 1 and DollarValue from Table 2, all the rows come up same .

I am just not able to figure it out.

 

Any guidance will be appreciated.

 

Need Help
  • MohammadLoran25's avatar
    MohammadLoran25
    3 years ago

    Okay _Regina ,

    As you have 'Table1'[ID] > -1 in your measure. It means:

    FILTER(ALL('Table1'[ID]),'Table1'[ID] > -1)

     

    So it would go through all the IDs (regardles of filter) and returns the same number for all of them.

     

    You need to change your measure as below to filter your entire table and work as you want:

     

     

    DollarValue CM =
VAR _current_date =
    CALCULATE ( MAX ( _date[Date] ) )
VAR _end_date =
    CALCULATE ( MAX ( 'Table2'[Date] ), 'Table2'[Date] <= _current_date )
VAR r =
    CALCULATE (
        SUM ( Table2[DollarValue ] ),
        '_date'[Date] = EOMONTH ( _end_date, 0 ),
        FILTER ( 'Table1', 'Table1'[ID] > -1 )
    )
RETURN
    r

     

     

    If this solves your problem, Please give it a thumbs up and accept it as a solution to make it easier for the others to find what they are looking for.

    Regards,
    Loran

     

     

     

3 Replies

  • MohammadLoran25's avatar
    MohammadLoran25
    Icon for Solution Sage rankSolution Sage
    3 years ago

    _Regina ,

    Not Possible. You are doing sth wrong or you are missing some info to share. please share a sample pbi file.

    • _Regina's avatar
      _Regina
      Icon for Helper I rankHelper I
      3 years ago

      DollarValue CM =

      var _current_date = CALCULATE(MAX(_date[Date]))
      var _end_date = CALCULATE(MAX('Table2'[Date]), 'Table2'[Date] <= _current_date)


      var r = CALCULATE(
      SUM(Table2[DollarValue ]),
      '_date'[Date] = EOMONTH(_end_date,0),
      'Table1'[ID] > -1
      )

      return r

       

       

      This is the measure that I am using.

       

      • MohammadLoran25's avatar
        MohammadLoran25
        Icon for Solution Sage rankSolution Sage
        3 years ago

        Okay _Regina ,

        As you have 'Table1'[ID] > -1 in your measure. It means:

        FILTER(ALL('Table1'[ID]),'Table1'[ID] > -1)

         

        So it would go through all the IDs (regardles of filter) and returns the same number for all of them.

         

        You need to change your measure as below to filter your entire table and work as you want:

         

         

        DollarValue CM =
VAR _current_date =
    CALCULATE ( MAX ( _date[Date] ) )
VAR _end_date =
    CALCULATE ( MAX ( 'Table2'[Date] ), 'Table2'[Date] <= _current_date )
VAR r =
    CALCULATE (
        SUM ( Table2[DollarValue ] ),
        '_date'[Date] = EOMONTH ( _end_date, 0 ),
        FILTER ( 'Table1', 'Table1'[ID] > -1 )
    )
RETURN
    r

         

         

        If this solves your problem, Please give it a thumbs up and accept it as a solution to make it easier for the others to find what they are looking for.

        Regards,
        Loran