Forum Discussion

thmtech's avatar
thmtech
New Member
7 years ago
Solved

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

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 t...
Need Help
thmtech's avatar
thmtech
New Member
7 years ago

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's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      thmtech
      New Member
      7 years ago

      Hi v-cherch-msft,

       

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

       

      ProductResource GroupTagsApplied Cost Center
      diskRG-1 D1
      diskRG-2 D2
      diskRG-2 D2
      blobRG-3{"Cost Center": "D3"}D3
      vmRG-1{"Cost Center": "D1"}D1
      vmRG-2{"Cost Center": "D2"}D2
      vmRG-2{"Cost Center": "D2"}D2
      networkRG-1{"Cost Center": "D1"}D1
      networkRG-2{"Cost Center": "D2"}D2
      networkRG-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!