How to determine new column values based on relationship to other columns and row values

[thmtech]

I am running the Microsoft Azure Consumption Insights service but am running into an issue. We tag all of our resources, hidden or otherwise, in Azure but there are always meter's that don't take a tag and therefore when I split out our "cost center" names we have blank (null) values.

How can I take the the Resource Group value for the current row, compare it to a different row that has a "Cost Center" value in it and then set the new column value to what the correlated Resource Groups, Cost Center value is?

My thought was this: 

correlatedcostcenter = 
VAR CurrentRG = Selectedvalue(UsageDetails[Resource Group]) 
VAR CurrentTag = SelectedValue(UsageDetails[Tags]) 
RETURN
IF(
ISERROR( 
SEARCH("Cost Center", UsageDetails[Tags]) 
), 
LOOKUPVALUE(UsageDetails[Tags],[Resource Group],CurrentRG), 
CurrentTag 
)

Basically I just want to be able to say (for now): "Hey, this row doesn't have a Cost Center tag. No problem, look for another row that has the same Resource Group as this one but has a Cost Center tag, then copy it's Cost Center tag to this row and move on."

Please help, i'm stuck!

[thmtech]

I've been massaging the column formula and here's what i've come up with thus far. It still doesn't catch everything though:

correlatedcostcenter = 
VAR CurrentRG = UsageDetails[Resource Group]
VAR CurrentTag = UsageDetails[Tags]
RETURN
IF(
    ISERROR(
        SEARCH("Cost Center", UsageDetails[Tags])
    ),
    CALCULATE(
        FIRSTNONBLANK(UsageDetails[Tags],TRUE()),
        FILTER (
            UsageDetails,
            UsageDetails[Resource Group] = EARLIER (UsageDetails[Resource Group])
        )
    ),
    CurrentTag
)

I tried using LookupValue but since there are numerous entries for the Resource Group in question, I needed a different way to just select the first entry and work against it. As before, any help is greatly appreciated!

[v-cherch-msft]

Hi @thmtech

Could you show us some simplified data sample for your scenario?Please refer to the example in this post and provide the sample data and expected output.These will be helpful to provide an accurate solution.

Regards,

Cherie

[thmtech]

Hi @v-cherch-msft,

Here's the best I could come up with, hopefully it helps.

Product	Resource Group	Tags	Applied Cost Center
disk	RG-1		D1
disk	RG-2		D2
disk	RG-2		D2
blob	RG-3	{"Cost Center": "D3"}	D3
vm	RG-1	{"Cost Center": "D1"}	D1
vm	RG-2	{"Cost Center": "D2"}	D2
vm	RG-2	{"Cost Center": "D2"}	D2
network	RG-1	{"Cost Center": "D1"}	D1
network	RG-2	{"Cost Center": "D2"}	D2
network	RG-3		D3

So what we're doing is for those rows which have the Tag "Cost Center" it is setting that value to the Applied Cost Center column for that row. For the rows that don't have a Cost Center tag, i need PowerBI to match to another row based on the Resource Group it has. So for instance the last row has no Cost Center tag but has Resource Group: RG-3. PowerBI would look for the first row that has RG-3 as it's Resource Group but also has a Cost Center tag applied, and then apply said tag to the original row that was being worked.

The "Applied Cost Center" column is the custom column where i need it to drop all this information based on the above methods. I have some other custom columns that are formatting that Tags column to remove the erroneous characters/words that I don't need to give me the final "nice" result in the Applied Cost Center column.

The rest of the data is pulled with the Microsoft Azure Consumption Insights service so I don't have a way to manipulate the incomming data before it gets to PowerBI (nor do I want to since I need to share this sheet with other departments so they can get live data).

Any help is really appreciated. Thank you for taking the time to read and digest!

[v-cherch-msft · Accepted solution]

Hi @thmtech

You may try below measure. Attached the sample file for your reference.

Column = 
CALCULATE (
    FIRSTNONBLANK ( Table1[Tags2], 1 ),
    FILTER (
        Table1,
        Table1[Resource Group] = EARLIER ( Table1[Resource Group] )
            && Table1[Tags2] <> BLANK ()
    )
)

Regards,

Cherie