Forum Discussion

Erf19's avatar
Erf19
Frequent Visitor
6 years ago

calculated column using measure

I am going crazy trying to figure this out. I'm sure it's something to do with trying to combine a calculated column and a measure but I don't know enough PowerBI to fix it/find a work around.

 

I have a table of data with 2 important attributes for each asset - delay cost and delay minutes.

 

I have created 2 columns called 'rank cost' and 'rank minutes' using the following formula (replacing cost with minutes where necessary of course):

Rank Cost = rankx(all(AssetDelayData),AssetDelayData[Delay Cost Rank],,ASC)
 
So each row is numbered from 1 to 267 depending on cost, and same again in another column depending on minutes.
 
I want to only display one column at a time, depending on whether Delay Cost or Delay Minutes is selected in a dropdown. This drop down/slicer is created from a separate table called with just these two values in.
 
I created the following measures:
Selected Type = SELECTEDVALUE('Table'[Type])
Multiplier = if([selected type]="Delay Cost",1,0)
 
By using a card visualisation I can see that this *does* work correctly and the value of Multiplier is either 1 or 0 depending on type selected from the dropdown.
 
Then I tried to create a new column called 'Rank Overall' using various methods, including:
 
1.
Rank Overall = if(SELECTEDVALUE('Table'[Type])="Delay Cost",AssetDelayData[Rank Cost],AssetDelayData[Rank Minutes])
This only ever returns Rank Minutes, even when Delay Cost is selected and Multiplier is 1.
 
2.
Rank Overall = if(SELECTEDVALUE('Table'[Type])="Delay Cost",AssetDelayData[Rank Cost],if(SELECTEDVALUE('Table'[Type])="Delay Minutes",AssetDelayData[Rank Minutes],"Error"))
Similar to (1.), this only ever shows "Error," - this was just me double checking that something is wrong.
 
3.
Rank Overall = AssetDelayData[Rank Cost] * Multiplier + AssetDelayData[Rank Minutes] * (1 - Multiplier)
However this still doesn't work since it still treats Multiplier as 0 even when Delay Cost is selected and the card confirms it is 1.
 
I have tried doing this with unpivoted data and a matrix, but it is then impossible to order the matrix rows by rank unfortunately.
 
I also tried using Switch but it still is the same problem, not registering that the if statement is true.
 
Where am I going wrong with this? What's the problem?
 
Thanks!
Need Help

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    Erf19 ,

     

    Could you please share some sample data and clarify more details about your requirement?

     

    Regards,

    Jimmy Tao