Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PBIAnonUser
Frequent Visitor

Want measure to return a column

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

 

 

 

 

 

4 REPLIES 4
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 

 

IndexKeyDatekeyIndexCodeDateTotalReturnRate
120200101I101/01/20208
120200102I102/01/20209
120200103I103/01/202013
120200104I104/01/202014
120200105I105/01/202014
220200101I201/01/20206
220200102I202/01/20209
220200103I203/01/202011
220200104I204/01/202012
220200105I205/01/202012
320200101I301/01/20207
320200102I302/01/20208
320200103I303/01/202010
320200104I304/01/202012
320200105I305/01/202012
420200101I401/01/20209
420200102I402/01/202011
420200103I403/01/202012
420200104I404/01/202011
420200105I405/01/20209
520200101I501/01/20208
520200102I502/01/20209
520200103I503/01/202013
520200104I504/01/202011
520200105I505/01/20201

 

Dim.Clients

 

ClientKeyClientCodeClientStartDateDatekey
1C104/01/202020200101
2C205/01/202020200102
3C303/01/202020200103
4C401/01/202020200104
5C502/01/202020200105

 

Dim.Index

IndexKeyIndexCode
1I1
2I2
3I3
4I4
5I5

 

Dim.Date

DatekeyDate
2020010101/01/2020
2020010202/01/2020
2020010303/01/2020
2020010404/01/2020
2020010505/01/2020

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

yingyinr_0-1658813416175.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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. 

 

Performance example.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.