topic Re: Creating Calculated Column Based on Measure in DAX Commands and Tips

Creating Calculated Column Based on Measure

eHorizons — Wed, 10 Apr 2024 17:21:04 GMT

Hi Everyone,

I'm trying to create a calculated column based on either a measure or a column in another table. I have tried both methods below and in both cases the column only calculates off the first condition and I get this result every time: 'T Tests'[Timepoint 1] - 'T Tests'[Timepoint 2])

Method 1: Creating conditional calculated column using a calculated measure in the same table

dif =

If([Group] = "Timepoint 1 vs 2", 'T Tests'[Timepoint 1] - 'T Tests'[Timepoint 2],

IF([Group]="Timepoint 2 vs 3", 'T Tests'[Timepoint 2] - 'T Tests'[Timepoint 3], 'T Tests'[First] - 'T Tests'[Most Recent]))

Method 2: Creating conditional calculated column using a column in another table

dif =

SWITCH(TRUE(),

FIRSTNONBLANK('Group'[Group],1)="Timepoint 1 vs 2",

'T Tests'[Timepoint 1] - 'T Tests'[Timepoint 2],

FIRSTNONBLANK('Group'[Group],1)="Timepoint 2 vs 3",

'T Tests'[Timepoint 2] - 'T Tests'[Timepoint 3],

'T Tests'[First] - 'T Tests'[Most Recent]

)

Any ideas who I can get the desired result? Thanks!

Re: Creating Calculated Column Based on Measure

Greg_Deckler — Wed, 10 Apr 2024 18:06:19 GMT

eHorizons Basing a calculated column on a measure will not work generally as measures are dynamic but calculated columns are only calculated at the time of data load. If basing this on a column from another table, generally use RELATED or LOOKUPVALUE or MAXX(FILTER(...), ...). Hard to be specific without sample data.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.