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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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