Want measure to return a column

[PBIAnonUser]

Hello,

I am trying to calculate a parameter called Performance. In TableA, we have a Total Return Rate and Date column. and seperately, TableClients

The equation is:

Performance(on a given day) = (TotalReturnRate(on a given day) / TotalReturnRate(on client start))-1

I wanted to have Performance as a calculated column, but I'm using DirectQuery Mode, so I need to use measures (as TRT on Client Start Date uses CALCULATE which is not allowed in for custom columns in DQ mode).

The issue is, TotalReturnRate(on a given day) isn't static, but TRT on a selected client's start date is.

Since I have to use a measure, I can't use the Fact.TableA[TotalReturnRate] column (columns used in measures must be wrapped un functions/aggregations)

I'm looking for a way to have a measure TotalReturnRate(on a given Date), behave like a normal column and return a different result for each date.

Can anyone help with this please? 

Thanks,

PBIAnonUser

[v-yiruan-msft]

Hi @PBIAnonUser ,

Since TableA[TotalReturnRate] is not static, but will be dynamically displayed as the user interaction with it, so we can't create a calculated column to achieve it. As the value of a calculated column is computed during data refresh and uses the current row as a context, and it does not depend on user interaction in the report. You can create a measure to display the data dynamically base on the user interactions. Please review the following links to get more details on the difference between calculated column and measure. Any comment or problem, please feel free to let me know.

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

In order to give you a suitable solution, could you please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It is better if you can share a simplified pbix file. Thank you.

Best Regards

[PBIAnonUser]

Hi, 

Thanks for taking the time to review this.  I've added a table of expected results for the selected index and client. Unfortunately, I cannot find the option to upload a file to the community (it mentioned in another thread that this might be because I'm relatively new?).

Anyways, I'll copy and paste the releavant tables in a reply below this.

Tables:
Fact.A 

IndexKey	Datekey	IndexCode	Date	TotalReturnRate
1	20200101	I1	01/01/2020	8
1	20200102	I1	02/01/2020	9
1	20200103	I1	03/01/2020	13
1	20200104	I1	04/01/2020	14
1	20200105	I1	05/01/2020	14
2	20200101	I2	01/01/2020	6
2	20200102	I2	02/01/2020	9
2	20200103	I2	03/01/2020	11
2	20200104	I2	04/01/2020	12
2	20200105	I2	05/01/2020	12
3	20200101	I3	01/01/2020	7
3	20200102	I3	02/01/2020	8
3	20200103	I3	03/01/2020	10
3	20200104	I3	04/01/2020	12
3	20200105	I3	05/01/2020	12
4	20200101	I4	01/01/2020	9
4	20200102	I4	02/01/2020	11
4	20200103	I4	03/01/2020	12
4	20200104	I4	04/01/2020	11
4	20200105	I4	05/01/2020	9
5	20200101	I5	01/01/2020	8
5	20200102	I5	02/01/2020	9
5	20200103	I5	03/01/2020	13
5	20200104	I5	04/01/2020	11
5	20200105	I5	05/01/2020	1

Dim.Clients

ClientKey	ClientCode	ClientStartDate	Datekey
1	C1	04/01/2020	20200101
2	C2	05/01/2020	20200102
3	C3	03/01/2020	20200103
4	C4	01/01/2020	20200104
5	C5	02/01/2020	20200105

Dim.Index

IndexKey	IndexCode
1	I1
2	I2
3	I3
4	I4
5	I5

Dim.Date

Datekey	Date
20200101	01/01/2020
20200102	02/01/2020
20200103	03/01/2020
20200104	04/01/2020
20200105	05/01/2020

[v-yiruan-msft]

Hi  @PBIAnonUser ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:

Performance = 
VAR _selddatekey =
    SELECTEDVALUE ( 'Date'[Datekey] )
VAR _selAdatekey =
    SELECTEDVALUE ( 'A'[Datekey] )
VAR _selindexcode =
    SELECTEDVALUE ( 'A'[IndexCode] )
VAR _returnrate =
    CALCULATE (
        MAX ( 'A'[TotalReturnRate] ),
        FILTER (
            ALLSELECTED ( 'A' ),
            'A'[IndexCode] = _selindexcode
                && 'A'[Datekey] = _selddatekey
        )
    )
RETURN
    DIVIDE ( SUM('A'[TotalReturnRate]), _returnrate ) - 1

Best Regards

[PBIAnonUser]

Hi,

This is close to what I want. I don't want to select a date in the slicer. I just want to select a client, an index, and for the measure to show performances for all dates.

If possible, I would like to have the performance done for all dates without having a date/datekey selected (or, if a date must be selected, showing performances for dates up until the selected date). 

I plan on plotting Performance against 'FactA'[Date]. I've attached an example picture of what I'm looking for as a result.