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 Dolla...
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

     

     

     

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