Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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
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")
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.
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
Hi @v-lili6-msft , thank you very much! Can you explain step by step how this code works?
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.)
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |