Please kindly help LOD Tableau in Nested Ifs DAX or something else?

[Anonymous]

Hi all,

I am hoping to get some guidance/solution from you as I could have it done previously in Tableau but I am not sure how to do this in Power Bi which is the current platform we are using at work.

Below is a mock data of the card transactions that our credit cardholders spent last month. I would like to filter on Transactions/cardholders based on Transaction ID who incurred Split Transactions. Split transactions are those if it meets both the following 2 business rules:

1. A transaction is part of the split transactions when the transactions have the same cardholder, same transaction date, same merchant AND

2. The total transaction amount of this split transactionS exceed the transaction limit.

In the below example, Amy H, John L and Sue A are the people who incurred split transactions.

-For Amy H, transaction ID 001,004,007 have the same cardholder, same transaction date, same merchant and the total transaction incurred in Bunning 2023 is $1900 ($1000+$400+$500) which is higher than her transaction limit of $500

-For John L, transaction 002 and 009 have the same cardholder, same transaction date, same merchant and the total transaction incurred in KFC Newington is $330 which is higher than her transaction limit of $100

-For Sue A, transaction 014 and 015 have the same cardholder, same transaction date, same merchant and the total transaction incurred in Michael Hill Jewellery is $1000 which is higher than her transaction limit of $500

There are other cardholder who met the 1st rule but not the 2nd rule where the total transactions is less than their transaction limit so they are fine.

I reallly appreciate if any of you can share your valuable experience with me in getting this solved. Thanks a million times.

Transaction ID	Cardholder	Card Number	Transaction Limit	Transaction Date	Merchant Name	Amount
001	Amy H	xxxx123	500	11/12/2020	Bunning 2023	1,000
002	John L	xxxx256	100	11/12/2020	KFC Newington	250
003	Christine F	xxxx389	100	11/12/2020	Office work 2000	80
004	Amy H	xxxx123	500	11/12/2020	Bunning 2023	400
005	Amy H	xxxx123	500	11/12/2020	Thai Restaurant Marricville	600
006	Frances F	xxxx788	1000	11/12/2020	Paypal Reading ABC	800
007	Amy H	xxxx123	500	11/12/2020	Bunning 2023	500
008	Morris M	xxxx1054	100	11/12/2020	Children Accessories	50
009	John L	xxxx256	100	11/12/2020	KFC Newington	80
010	Christine F	xxxx389	100	11/12/2020	Coles Newmarket	50
011	Frances F	xxxx788	1000	12/12/2020	Apple Shop 2560	500
012	Sabrina L	xxxx1586	100	12/12/2020	Camping Shop 9999	100
013	Macdona N	xxxx1719	100	12/12/2020	Shoesbox Carington	80
014	Sue A	xxxx1852	500	12/12/2020	Michael Hill Jewels	600
015	Sue A	xxxx1852	500	12/12/2020	Michael Hill Jewels	400
016	Amy H	xxxx123	500	12/12/2020	Woolworth Stafford	200
017	John L	xxxx256	100	12/12/2020	Aircon Repair Service	60
018	Sammie F	xxxx2384	100	12/12/2020	The GoodGuy Evermore	67
019	Ron M	xxxx2517	100	12/12/2020	Café Local	20
020	Frances F	xxxx788	1000	12/12/2020	Apple Shop 2560	200
021	Nicole N	xxxx2783	100	12/12/2020	Café Local	30
022	Jasmine L	xxxx2916	100	12/12/2020	Camping Shop 9999	40
023	Margaret M	xxxx3049	100	13/12/2020	office work windsor	99
024	Sue A	xxxx1852	500	13/12/2020	curtain home corner	450
025	Ron M	xxxx2517	100	13/12/2020	jewlels repair Amazon	6
026	Leon S	xxxx3448	100	13/12/2020	Bakery community	66
027	Amy H	xxxx123	500	13/12/2020	Officework Westsryde	22
028	Morris M	xxxx1054	100	13/12/2020	Children Accessories	99
029	Susan Z	xxxx3847	200	13/12/2020	Au Post service	160
030	Susan Z	xxxx3847	200	13/12/2020	Sushi Corner	36

[lbendlin]

you don't need nested IFs for that. Use the "&&" (and)  and "||" (or) operators as needed.

Having said that, the first part of your problem is more one of grouping, rather than logic checks. After the grouping, do a SUMX on the result and then check if the SUMX exceeds the threshold.

[Anonymous]

Hi 

Thanks for the reply, can you please explain a bit more. I m not sure how to compared transactions for instance, how to do "if transaction 001,004,007 is part of the split transaction because the cardholder is the same person, same merchant and same day??