How to write Tableau formula in Power BI (fixed:max with if-function)

[Anonymous]

Hi,

I could like to write the following Tableau formula in Power BI:

VIP =
if {
         fixed [accountnumber], [created_date] : max(
                  if [product_name] = 'amount of badges 0/1/2/3' AND [amount] > 0
                  then 1 else 0 end)
} = 1 then 'VIP' ELSE 'Not VIP'
END

I've been struggling for 2 days and I can't find any solution. I''ve tried the following, but I get a syntax error:

VIP =
if (
         calculate(
                  max(
                            if ‘list of products'[product_name] = "amount of badges 0/1/2/3" and [amount] > 0 ; 1 ; 0
                  ),
                  allexcept([accountnumber]),
                  allexcept([created_date])
         )
) = 1 ; "VIP" ; "Not VIP"

This is an example of how the table looks like:

account_number	product_name	amount
123456	amount of badges 0/1/2/3	0
123456	apple	60
123456	banana	2
551155	milk	75
551155	cheese	45
551155	banana	3
333333	amount of badges 0/1/2/3	2
333333	apple	6
333333	cheese	7

I would like to create a VIP column or at least a measure so I can use it as a filter (Slicer).

So if amount of badges is 1, 2 or 3 all this account_number should get a VIP status, even if the product_name is apple.

account_number	product_name	amount	VIP
123456	amount of badges 0/1/2/3	0	Not VIP
123456	apple	60	Not VIP
123456	banana	2	Not VIP
551155	milk	75	Not VIP
551155	cheese	45	Not VIP
551155	banana	3	Not VIP
333333	amount of badges 0/1/2/3	2	VIP
333333	apple	6	VIP
333333	cheese	7	VIP

[Greg_Deckler]

Here is a column version:

VIP = 
VAR __table = FILTER(ALL('Table 5'),[account_number] = EARLIER([account_number]))
VAR __table1 = FILTER(__table,[amount] = 1 || [amount] = 2 || [amount] = 3)
VAR __table2 = FILTER(__table1,[product_name] = "amount of badges 0/1/2/3")
RETURN
IF(COUNTROWS(__table2),"VIP","Not VIP")

[Anonymous]

Hi Greg! You did answer the question I asked, but I forgot to ask one thing. I also want to take the latest created_date.

Here is the table 'list of products' before:

Here is the table 'list of products' after:

123456 had 3 badges on January 8, but it changed to 0 badges on January 15. So he was a VIP, but now he is not VIP anymore.

551155 had 0 badges on February 25, but it changed to 1 badge on March 1. So he was not VIP, but now he is VIP.

333333 had 2 badges on March 13 and it changed to 3 badges on March 20. So he was VIP and is still VIP.

Which formula should I add to take the latest/max created_date?

Note: There are more columns in the table including account name, group name, etc.

[v-lili6-msft · Accepted solution]

HI, @Anonymous 

Just add a conditional in the formula as below:

VIP = 
VAR __table =
    FILTER ( ALL ( 'Table 5' ), [account_number] = EARLIER ( [account_number] ) )
VAR __table1 =
    FILTER ( __table, [amount] = 1 || [amount] = 2 || [amount] = 3 )
VAR __table2 =
    FILTER ( __table1, [product_name] = "amount of badges 0/1/2/3" )
RETURN
    IF (
        CALCULATE ( MAXX ( __table2, [created_date] ) )
            = CALCULATE (
                MAX ( 'Table 5'[created_date] ),
                FILTER (
                    'Table 5',
                    'Table 5'[account_number] = EARLIER ( 'Table 5'[account_number] )
                        && 'Table 5'[product_name] = "amount of badges 0/1/2/3"
                )
            ),
        "VIP",
        "Not VIP"
    )

Result:

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft , thank you very much! Can you explain step by step how this code works?

[Anonymous]

Hi Greg! You did answer the question I asked, but I forgot to ask one thing. I also want to take the latest created_date.
Here is the table 'list of products' before:

Here is the table 'list of products' after:

123456 had 3 badges on January 8, but it changed to 0 badges on January 15. So he was a VIP, but now he is not VIP anymore.
551155 had 0 badges on February 25, but it changed to 1 badge on March 1. So he was not VIP, but now he is VIP.
333333 had 2 badges on March 13 and it changed to 3 badges on March 20. So he was VIP and is still VIP.
Which formula should I add to take the latest/max created_date?

Note: I have more columns (for example: account name, group etc.)