IFBLANK function for metrics

Hello, 

I have a feeling you are not using the “Arrow” parameter in your mapping metric, which can be used to control, which dimensions are replaced or added in a mapping. 

You can find more details from here: 

Here is an example of metrics and formulas:

• MET_Amount
  • Input metric with Region dimension
• MET_Mapping_Rule_1:
  • Metric for the first mapping rule
  • Dimensions are Region 
  • Data type is Cost Center (amount is being mapped to a Cost Center)
• MET_Mapping_Rule_2:
  • Metric for the second mapping rule
  • Dimensions are Region 
  • Data type is Cost Center (amount is being mapped to a Cost Center)
• MET_Amount_Final
  • Calculation for the mapped amount
  • Dimensions are Region and Cost Center
  • Formula: 
    • IFDEFINED(MET_Mapping_Rule_1, MET_Amount[BY:->MET_Mapping_Rule_1], MET_Amount[BY: ->MET_Mapping_Rule_2])
  • Notice the use of the arrow after the BY: statement. I suggest trying this out in the playground to see the impact with and without the arrow. 

Hope this helps :) 

Hey,
Thanks for your response.

The structure of both my metrics are values and not dimensions.
I’m trying to map an expense to its respective KPI line first based on the “Vendor” dimension and if not available and returns blank it should map to the KPI line based on the “Expense type” dimension.

I want the expense item to be mapped to the KPI line either through one of these basis only and not both. This is where my IFBLANK or IFDEFINED didn’t work. It ignores my condition and duplicates the final value.
 

To add on the KPI line item is a dimension - So map the expense amount to a KPI dimension based on vendor (dimension) but if the result is blank (and only if blank) map the expense to a KPI dimension based on the expense type (dimension).

Hey, 
Not sure if I yet fully understood what you mean, but let’s try a different approach. 

Let’s say you have three dimension lists:

• Expense Types
• Vendors
• KPI List

Here are screenshots of my lists:

So both Vendor and Expense Type lists have a property mapping to a specific KPI. 

Next we would have some values in a metric like this:

Total for Jan 24 = 1600

Total for Feb 24 = 1750

You will see that there are some combinations that could be mapped based on the KPI property from Vendor list and Expense Type list. 

To sum the figures in a final metric without duplication, you can use the FILTER option to filter only the relevant amounts. 

Formula here:

// Adding amounts based on Vendor mapping to KPI group
MET_INP_Amount[FILTER:ISNOTBLANK(Vendors.'KPI Group')][BY SUM:Vendors.'KPI Group'][REMOVE:'Expense Types'] 

// Adding amounts based on Expense Type mapping to KPI group
+ MET_INP_Amount[FILTER:ISNOTBLANK('Expense Types'.'KPI Group') AND ISBLANK(Vendors.'KPI Group')][BY SUM:'Expense Types'.'KPI Group'][REMOVE:Vendors]