Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
thmtech
New Member

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 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!

1 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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
thmtech
New Member

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!

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.