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

[_Regina]

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.

[MohammadLoran25]

@_Regina ,

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

[_Regina]

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 · Accepted solution]

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