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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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

 

1 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:

4.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
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

5 REPLIES 5
Greg_Deckler
Super User
Super User

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")

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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:

badges before.PNG

Here is the table 'list of products' after:

badges after.PNG

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.

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:

4.JPG

 

Best Regards,

Lin

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

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

Anonymous
Not applicable

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:
badges before.PNG
Here is the table 'list of products' after:
badges after.PNG
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.)

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.