Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

How to search a column values from another table column using IN Operator

I have  6 columns as below:

Table Name: Fixed_Deposit

Customer IDAccount NoAccount TypeBase ValueCurrent ValueFlag
12345100011010Fixed Deposit100011111
12345100001000Saving Account1001110
12345100100100Saving Account100000100000
21223123312233Fixed Deposit100011122230
21223123323212Saving Account1010112231230
31221231233212Fixed Deposit122121233211
31221123321332Current Account2312123310
32222433434312Fixed Deposit222111222120
12345233323232Current Account222212212220

 

Now, I have a flag set to 1 for all the Fixed Deposit accounts. I need the sum of their Base Amount and Current Amount as 2 seperate columns in the matrix.

How do I get this?

 

I have used the below dax query:

I have created a new table using below Dax:

Unique_Customer = CALCULATETABLE(DISTINCT(Fixed_Deposit[Customer ID]),Fixed_Deposit[Flag]=1)
 
Then I created a measure on my base table using below query:
FD CA Base = var a = DISTINCT(Unique_customer[Customer ID])
return
CALCULATE(SUM(Fixed_Deposit[Base Value])/10000000,FILTER(Fixed_Deposit,Fixed_Deposit[Customer ID] in a),FILTER(Fixed_Deposit,Fixed_Deposit[Account Type]="Current Account"))
 
Respectively for Savings Account and Fixed Deposit Accounts. 
 
Required Output:
Customer IDBase CA AmountBase SA AmountBase FD Amount
1234522221001001000
312212312012212

 

 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Greg_Deckler - Base CA means Base Value for Account Type =  "Current Account"

    Similarly for Base SA and Base FD.