SQL translation of IF statement to PowerBi Dax Query-CodePudding

I'm kinda new to DAX and PowerBi and I need to translate my SQL IF statement for whatever syntax is this on PowerBi to achieve the output I want.

Sql code I want to translate:

IF (Payment.payment>0) AND (Account.PV = Account.GV) THEN 1 ELSE 0

I want to make a calculated column on Payment table which will return 1 or 0 so that I can use this to filter all the records that meets my condition

account_id is the relationship of these two tables

Here is a sample data for reference: Account table

account_id	pv	gv	due_date
123	100	200	08/08/2022
124	200	200	08/09/2022
125	300	800	08/10/2022
126	400	670	08/11/2022
127	500	500	08/12/2022
128	600	600	08/13/2022
129	700	1000	08/14/2022
130	800	760	08/15/2022
131	900	900	08/16/2022
132	1000	1000	08/17/2022
133	1100	2300	08/09/2022

Here is a sample data for reference: Payment table

payment_id	payment_number	payment	payment_date	account_id	_test
101	554321	1000	03/01/2022	123	0
102	554322	1200	03/21/2022	124	1
103	554322	1100	04/28/2022	124	1
104	554323	2500	05/04/2022	131	1
105	554324	3000	05/14/2022	133	0
106	554325	3000	05/14/2022	132	1
107	554322	1200	03/21/2022	124	1
108	554323	2500	04/05/2022	131	1
109	554328	3100	04/05/2022	128	0

Codes I tried but I can't help myself to find the correct way to do it correctly and return the output that I need

_test = IF(Payments[payment]>0 && RELATED('Account'[PV])=RELATED('Account'[GV]), 1)
_test = IF(AND(Payments[payment])>0, RELATED('Account'[PV])=RELATED('Account'[GV])),1,0)

Any suggestion is much appreciated. Please recommend what kind of syntax/function should be used in order to achieve the output or what would be the work around to use other than IF statement

CodePudding user response：

The problem that you are facing with RELATED is that RELATED only works from 1 side to many side.

Meaning, that if you bring the axis from 1-side and perform a calculation on the many side the filter works perfectly. Take a look at the direction of the filter below. The direction of the filter tells you on normal circumstances, you should bring your axis from Account and whatever calculation you perform on `Payment table it will work out.

But you are doing exactly the reverse. You are bringing the axis from Payment and hoping for RELATED to work. It won't cause the direction to be as such.

However, DAX is much more dynamic than that. If for whatever reason, you need to bring axis from many side where you need to still filter on 1-side, you can define a reverse filter direction on-the-fly (because DAX is magical) without needing to change anything in the data model by using CROSSFILTER. With CROSSFILTER you are customizing the filter direction as such

CROSSFILTER(<LEFtblcolumnName1>, <RIGHTtblcolumnName2>, <direction>)

This is how, (with your given dataset)

Column = 
VAR cond1 =
    CALCULATE (
        MAX ( Account[Account.pv] ),
        CROSSFILTER ( Payment[Payment.account_id], Account[Account.account_id], BOTH )
    )
        - CALCULATE (
            MAX ( Account[Account.gv] ),
            CROSSFILTER ( Payment[Payment.account_id], Account[Account.account_id], BOTH )
        )
RETURN
    IF ( cond1 == 0 && Payment[Payment.payment] > 0, 1, 0 )