CODEWITHSUNDEEP
powerbi
Ro_Ba
Ro_Ba

Reputation: 33

Copy value from another table based on parameter

enter image description here

This is the code I have use and it shows an error. I am checking where the period values match and copying data from table where the period values are the column names.

Column = IF(AND(BillingData[Period] = 1,
MINX( 
RELATEDTABLE(AdminTarget),
AdminTarget[1]))

I tried this code initially to test out just one period and it works. But I want to do it for all the periods.

Any help would be highly appreciated as I am new to PowerBI.

Upvotes: 0

Views: 70

Answers (2)

MikeAinOz
MikeAinOz

Reputation: 128

I think you're half-way there, TREATAS might get you over the line, here's how I used it recently for a Measure, which you may find has more utility than a column.

LatestPrice = 
Var MaxId = CALCULATE(Max(CryptoQuote[Id]),TREATAS(Values(Crypto_Spark[Symbol]),CryptoQuote[Symbol]))
RETURN CALCULATE(MAX(CryptoQuote[Price]),CryptoQuote[Id]=MaxId)

This just creates a dynamic relationship between the two tables so that a filter on CryptoSpark is used on CryptoQuote. In your case you'd TREATAS(Values(BillingData[Period]),(AdminTarget[ID])) TREATAS is really handy but I found it hard to get my head around this is a good link to explain it, with a nice example TREATAS. I hope this helps, the PowerBI journey is always challenging, but rewarding

Upvotes: 2

mkRabbani
mkRabbani

Reputation: 16908

If both your table BillingData and AdminTarget has relation with some column like ID, you can use this below code for your new Custom Column-

Column 2 = 
SWITCH(
    TRUE(),
    BillingData[Period] = "1",  RELATED(AdminTarget[1]),
    BillingData[Period] = "2",  RELATED(AdminTarget[2]),
    BillingData[Period] = "3",  RELATED(AdminTarget[3]),
    BillingData[Period] = "4",  RELATED(AdminTarget[4]),
    BillingData[Period] = "5",  RELATED(AdminTarget[5]),
    BillingData[Period] = "6",  RELATED(AdminTarget[6]),
    BillingData[Period] = "7",  RELATED(AdminTarget[7]),
    BillingData[Period] = "8",  RELATED(AdminTarget[8]),
    BillingData[Period] = "9",  RELATED(AdminTarget[9]),
    BillingData[Period] = "10", RELATED(AdminTarget[10]),
    BillingData[Period] = "11", RELATED(AdminTarget[11]),
    BillingData[Period] = "12", RELATED(AdminTarget[12])

)

Upvotes: 1

Related Questions