Populate blank values from matching records

[rixmcx59]

Hello all, I am trying to populate a column [Tags] or new column, the un-tagged columns have values that match other records with Tags like ResourceName, ResourceType and ResourceGroup. It appears I may have to create a new column to do this correct?

The logic would be, IF(ISBALNK([TAGS] then find a matching ResourceName and ResourceType with a tag and use the tag OR if there is no matching ResourceName use the ResourceGroup to match on.

Was not sure which function to use? Match, search, Find, Replace?

Thank you

[v-juanli-msft]

Hi @rixmcx59 

If i understand you right,

Create a measure

Measure =
VAR t1 =
    IF (
        SEARCH ( "N/A", MAX ( Sheet4[Tags] ), 1, 0 ),
        CALCULATE (
            MAX ( Sheet4[Tags] ),
            ALLEXCEPT ( Sheet4, Sheet4[Resource Name], Sheet4[Resource Type] )
        ),
        MAX ( Sheet4[Tags] )
    )
RETURN
    IF (
        SEARCH ( "N/A", t1, 1, 0 ),
        CALCULATE ( MAX ( Sheet4[Tags] ), ALLEXCEPT ( Sheet4, Sheet4[Resource Group] ) ),
        t1
    )

Best Regards

Maggie

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

[rixmcx59]

Maggie, thank you for the solution, however in my result I am getting {"TagInfo":"N/A"}, not the Tag for the group. Anyway, I realized this won't work, I just discovered that groups have as many as 48 different tags so there goes my logic.

What I am going to try is create a table with distinct values for group name, then get the actual tag from the Azure resource group page. In Azure each resource has the current tag.

[rixmcx59]

Hello, I now have a table, DictinctResouceGroup, the table has the unique name and tag for each group. I did not create a relationship.

Instead of looking for a matching resource group with a tag , I would like to use the new table and join where resource group = resource group.

for example when no resource name is found, join resource group On related resource group and use the tag from the related table. 

Is there a way to add an if and then use the related table value to the DAX below

NewCostCenter = 
VAR RsourceName = UsageDetails[Resource Name]
RETURN
    CALCULATE (
        FIRSTNONBLANK ( UsageDetails[Tags - Copy.Cost Center], 1 ),
        FILTER (
            ALL ( UsageDetails),
            UsageDetails[Resource Name] = RsourceName
                && UsageDetails[Tags - Copy.Cost Center] <> BLANK ()
                && UsageDetails[Tags - Copy.Cost Center] <> ""
        )
    )

[v-juanli-msft]

Hi @rixmcx59 

Based on my understanding, your requirement should be as below:

ISBALNK([TAGS] -> the value of "Tags" column is { "TagInfo": "N/A" }

IF(ISBALNK([TAGS] then find a matching ResourceName and ResourceType with a tag and use the tag OR if there is no matching ResourceName use the ResourceGroup to match on.

->IF, ISBALNK([TAGS],

           then If find a matching ResourceName and ResourceType with a tag, then use tag,

                       else 

                       there is no matching ResourceName, then use the ResourceGroup to match on.

           else 

           [TAGS]

Best Regards

Maggie

[rixmcx59]

Yes, exactly right Maggie. I will test this asap. thanks again

[v-juanli-msft]

Hi @rixmcx59 

Do [TAGS], [ResourceName],[ResourceType],[ResourceGroup] exist in the same table?

Could you show example data?

Best Regards

Maggie

[rixmcx59]

Yes, all the fields are in a single table.

Resource Name	Resource Type	Resource Group	Cost	Tags
backupprodsnapshots	storageAccounts	PROD-ASE01-067-RG	$1.55	{  "Account Code": "18BCP00006",  "Cost Center": "157",  "Customer ID": "CDT",  "Project": "SHAR",  "Project Name": "Shared production resource",  "Rate Code": "K701"}
backupprodsnapshots	storageAccounts	PROD-ASE01-067-RG	$3.80	{ "TagInfo": "N/A" }
nsgprodflowlogs	storageAccounts	PROD-ASE01-067-RG	$3.96	{  "Account Code": "18BCP00006",  "Cost Center": "157",  "Customer ID": "CDT",  "Project": "SHAR",  "Project Name": "Shared production resource",  "Rate Code": "K701"}
nsgprodflowlogs	storageAccounts	PROD-ASE01-067-RG	$211.24	{ "TagInfo": "N/A" }
prodase01067rgdiag	storageAccounts	PROD-ASE01-067-RG	$13.09	{  "Account Code": "18BCP00006",  "Cost Center": "157",  "Customer ID": "CDT",  "Project": "SHAR",  "Project Name": "Shared production resource",  "Rate Code": "K701"}
prodase01067rgdiag	storageAccounts	PROD-ASE01-067-RG	$51.47	{ "TagInfo": "N/A" }
prodase01cdtlogs	storageAccounts	PROD-ASE01-067-RG	$0.14	{  "Account Code": "18BCP00006",  "Cost Center": "157",  "Customer ID": "CDT",  "Project": "SHAR",  "Project Name": "Shared production resource",  "Rate Code": "K701"}
prodase01cdtlogs	storageAccounts	PROD-ASE01-067-RG	$0.54	{ "TagInfo": "N/A" }

[rixmcx59]

NewCostCenter = 
VAR RsourceName = UsageDetails[Resource Name]
RETURN
    CALCULATE (
        FIRSTNONBLANK ( UsageDetails[Tags - Copy.Cost Center], 1 ),
        FILTER (
            ALL ( UsageDetails),
            UsageDetails[Resource Name] = RsourceName
                && UsageDetails[Tags - Copy.Cost Center] <> BLANK ()
                && UsageDetails[Tags - Copy.Cost Center] <> ""
        )
    )

Almost there. Is there a way to add a condition to the above, if there is no match on Resource Name, then use Resource Group. Some of the Resource Names without Tags do not have a match, in that case I would get the Tag by matching on resource group.