How to group by IDs and show whether a product exists under the IDs and flag them

[flywheel_1987]

Hello,

I recently took over a colleagues work as he left. I am not a BI developer but I do have some experiences with PowerBI. I have this table:

A customer ID can have multiple products. I am trying to find Checking Only, Loan Only Customers, etc customer. I need to group by Account number. The whole thing is coming from an SSAS model so I can't create a conditional column in the PowerBI. I believe I have to use a variable table or some sort and summarize by ID and product type. 

This is what I want to show as I want to have flags so that I can create other measurements:

The logic I have created wouldn't work since I am looking as columns but they are coming as rows:

Here is a DAX measurement I have created (obviously it doesnt work):

Checking_Only Flag =

VAR CheckingAccts = CALCULATE(Count(Accounts [Accounts ID]), Products [Product Description] = "Checking")

VAR SavingsAccts = CALCULATE(Count(Accounts [Accounts ID]), Products [Product Description] = "Savings")

VAR LoansAccts = CALCULATE(Count(Accounts [Accounts ID]), Products [Product Description] = "Loan")

VAR TimeDepositAccts = CALCULATE (Count(Accounts [Accounts ID]), Products [Product Description] = "Time Deposit")

VAR MoneyMarketAccts = CALCULATE (Count(Accounts [Accounts ID]), Products[Product Description] = "Money Market")

VAR CreditCardAccts = CALCULATE(Count(Accounts [Accounts ID]), Products [Product Description] = "Credit Card")

VAR CustomerCount = CALCULATE(DISTINCTCOUNT (Customers [CustomerID]), CheckingAccts > 0, SavingsAccts = 0, TimeDepositAccts = 0,
MoneyMarketAccts = 0, CreditCardAccts = 0)

Return CustomerCount

The other way I wanted to do is:

Only Flag =

VAR CheckingAccts = CALCULATE(Count(Accounts [Accounts ID]), Products [Product Description] = "Checking")

VAR SavingsAccts = CALCULATE(Count(Accounts [Accounts ID]), Products [Product Description] = "Savings")

VAR LoansAccts = CALCULATE(Count(Accounts [Accounts ID]), Products [Product Description] = "Loan")

VAR TimeDepositAccts = CALCULATE (Count(Accounts [Accounts ID]), Products [Product Description] = "Time Deposit")

VAR MoneyMarketAccts = CALCULATE (Count(Accounts [Accounts ID]), Products[Product Description] = "Money Market")

VAR CreditCardAccts = CALCULATE(Count(Accounts [Accounts ID]), Products [Product Description] = "Credit Card")

VAR Logic =
IF(CheckingAccts > 0 && SavingsAccts = 0 && LoansAccts = 0 && TimeDepositAccts = 0 && CreditCardAccts = 0 && MoneyMarketAccts = 0, "Checking"

, IF(CheckingAccts = 0 && SavingsAccts > 0 && LoansAccts = 0 && TimeDepositAccts = 0 && CreditCardAccts = 0 && MoneyMarketAccts = 0, "Savings"

, IF(CheckingAccts = 0 && SavingsAccts = 0 && LoansAccts  > 0 && TimeDepositAccts = 0 && CreditCardAccts = 0 && MoneyMarketAccts = 0, “Loans”

, IF(CheckingAccts = 0 && SavingsAccts = 0 && LoansAccts = 0 && TimeDepositAccts > 0 && CreditCardAccts = 0 && MoneyMarketAccts = 0, “Time “Deposit

, IF(CheckingAccts = 0 && SavingsAccts = 0 && LoansAccts = 0 && TimeDepositAccts = 0 && CreditCardAccts > 0 && MoneyMarketAccts = 0, “Credit Cards”

, IF(CheckingAccts = 0 && SavingsAccts = 0 && LoansAccts = 0 && TimeDepositAccts = 0 && CreditCardAccts = 0 && MoneyMarketAccts = 0, "Savings”,

 “NA”)))))

RETURN Logic

I am assuming I have to utilize a function called addcolumn or addtable or something to create a variable table through measurement and use that for figuring out how many "Only" customers exists so that I can select or filter whichever only customer I want to look at.

Any help on this request will be extremely helpful. Thank you for reading this.

[amitchandak · Accepted solution]

@flywheel_1987 , you have to create a measure like the below, Add additional conditions

Measure =
Var _cnt = CALCULATE(COUNTROWS(Fact), allexcept(Fact, Fact[Customer id]))
Var _Checking_Only = CALCULATE(COUNTROWS(Fact), allexcept(Fact, Fact[Customer id]), Products[Product Description] = "Checking")
Var _Loan_Only = CALCULATE(COUNTROWS(Fact), allexcept(Fact, Fact[Customer id]), Products[Product Description] = "Loan")

Return
Switch( True() ,
_cnt = _Checking_Only && _Checking_Only >0 , "Only Checking",
_cnt = _Loan_Only && _Checking_Only >0 , "Only Load"
)

[flywheel_1987]

This is awesome! Thank you very much. Do you know if there is a way to count these flags? I need get these numbers for another calculation and show them as card for each "Only Flags". 

[amitchandak]

@flywheel_1987 , one way is individual measures, another is segmentation

very similar to

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

[flywheel_1987]

This is exactly what I needed. Thank you so much for all your help.