Measure Displaying an Item with Minimal Value Based on a Measure (Excluding Blanks and Zeros)

[devitus]

Hi,

I would like to kindly ask for help with the following. I'm building measures that would produce items with maximum and minimum values based on a measure.

Maximum is currently calculated as follows, and works as intended (it's part of the larger measure calculation, therefore is reprecented by a VAR):

VAR MaxSpendCampaignType = CALCULATE(LASTNONBLANK('03. Campaign Type (Search)'[Campaign Type], 1), FILTER(TOPN(1, VALUES('03. Campaign Type (Search)'[Campaign Type]),'01. Advertising Metrics'[NOM 01. Ad Spend],DESC), TRUE()), FILTER('02. Advertising Channel', '02. Advertising Channel'[Advertising Channel] = "Search"))

I've tried reversing the Maximum measure in order to produce the Mimimum one, but that didn't work. Therefore I've employed the following method:

VAR MinSpendCampaignType = CONCATENATEX(FILTER('03. Campaign Type (Search)', RANKX('03. Campaign Type (Search)', '01. Advertising Metrics'[NOM 01. Ad Spend],, ASC, Dense) = 1), '03. Campaign Type (Search)'[Campaign Type])

The below method for establishing the item with the mimimal value works in cases where none of the "Campaign Type" items have 0 for "NOM 01. Ad Spend". When "NOM 01. Ad Spend" is 0 for one of the "Campaign Type" items, it would output that for this VAR. When multiple "Campaign Type" items have 0 for "NOM 01. Ad Spend", they get concatenated into a string contraining a collection of those items.

I would like "Campaign Type" items with blanks and zeros for "NOM 01. Ad Spend" to be excluded from the ranking, and only the "Campaign Type" items with the lowest non-blank & non-zero "NOM 01. Ad Spend" to be the output of VAR MinSpendCampaignType.

Can you please suggest how to change current calculation, or suggest a better method of achieving this?

Thanks in advance!

[ValtteriN]

Hi,

You could try using MAXX and MINX to achieve what you are trying to do. So e.g. MAXX(Table,[Measure])

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[devitus]

Hi, thanks for your suggestion, but how would using MINX/MAXX filter out the "Campaign Type" results whose "NOM 01. Ad Spend" equals zero?

[ValtteriN]

Hi,

For using MINX and MAXX I was thinking that you could utilize FILTER in the table part of the formula as you have been using in your current calculations. By using FILTER in combination with the functions you can add conditions. 

Another way would be to use CALCULATE(MAX/MIN("Desired column"),"Filter conditions"). I often use
this pattern to e.g. get the latest date/latest value that I am looking for:

CALCULATE(MAX(table[DateColumn]),ALL(Calendar),table[DateColumn]<=MAX(Calendar[Date])

The Same logic can be applied to MIN in reverse. 

[devitus]

Hey, thanks! I've tried everything, but it still doesn't work all the way.

I've succeeded at producing a SpendRank measure that would rank "Campaign Type" by spend from largest to smallest while ignoring blanks (zeroes are fine, it's the blanks that had been sneaking in and causing the issue before).

Before:

SpendRank = RANKX(ALLSELECTED('03. Campaign Type (Search)'[Campaign Type]), '01. Advertising Metrics'[NOM 01. Ad Spend], ,DESC, Skip)

After:

SpendRank = IF(NOT(ISBLANK('01. Advertising Metrics'[NOM 01. Ad Spend])), RANKX(FILTER(ALLSELECTED('03. Campaign Type (Search)'[Campaign Type]), '01. Advertising Metrics'[NOM 01. Ad Spend] <> BLANK()), '01. Advertising Metrics'[NOM 01. Ad Spend], ,ASC, Skip))

But when I try to produce the Campaign Type value based on SpendRank like so:

SpendRankBottomItem = CONCATENATEX(FILTER('03. Campaign Type (Search)', RANKX('03. Campaign Type (Search)', '01. Advertising Metrics'[SpendRank],, ASC, Dense) = 1), '03. Campaign Type (Search)'[Campaign Type])

It outputs the following: "Sponsored BrandsSponsored Brands Video" (which are exactly the values with blank "NOM 01. Ad Spend" that I tried to remove from SpendRank in the first place.

It appears that the SpendRankBottomItem measure may be ignoring the filters applied. How can I update the SpendRankBottomItem measure to respect the filters, or is there another apparrent issue that's causing this?

Thanks so much once again 🙂

[v-yiruan-msft]

Hi @devitus ,

You can update the formula of your measure [SpendRankBottomItem] as below and check whether it can get the correct result:

SpendRankBottomItem =
CONCATENATEX (
    FILTER ( '03. Campaign Type (Search)', [SpendRank] = 1 ),
    '03. Campaign Type (Search)'[Campaign Type]
)

If the above one can't help you, please provide some sample data in table 01. Advertising Metrics and 03. Campaign Type (Search) (exclude sensitive data) and the correct result with calculation logic and special examples. Please also share the relationship info if there is any relationship created between these two tables. Thank you.

Best Regards