Convert SQL to Power BI

[joaovb96]

Hello everybody.
I have this Select in SQL:

select sum(VL_SALDO) from RECEBER r

where not r.COD_SITUAC not in ('2', '7', '8') and coalesce(r.CH_ATIVO, 'T') = 'T'

and r.DT_VENCIM < current_date

This select is intended to return the total amount of overdue accounts receivable. The select SQL returns the value 3,326,100.87.
Does anyone have any idea how I could convert this select into a dax measure in Power BI to bring the same value?
I've found a few things in my searches, but so far none has really worked.

[joaovb96]

HI @Pragati11 @Greg_Deckler 
Thanks for answering. (sorry for my bad inglish)

Yes, all columns come from the same table

I tried to run as I was told earlier, but it didn't work, I tried changing the 'T' to True and later changing the column to True/False but it didn't work either.

I'll try to explain before then what this SELECT would be.
SELECT aims to show accounts receivable that are overdue.
select sum(VL_SALDO) from RECEIVE r
(this line will add up the amount to be received)

where not r.COD_SITUAC not in ('2', '7', '8') and coalesce(r.CH_ACTIVE, 'T') = 'T'
(This line will only filter the accounts receivable that do not have the code 2,7,8 and must also have the code T of TRUE

and r.DT_VENCIM < current_date
(and this is to say that the account receivable date has to be less than the current date, therefore it is late)

[smpa01 · Accepted solution]

@joaovb96  DAX measure 

Measure =  --direct translation
CALCULATE (
    SUM ( 'COD_SITUAC'[VL_SALDO] ),
    FILTER (
        'COD_SITUAC',
        NOT 'COD_SITUAC'[COD_SITUAC]
            IN { 2, 7, 8 }
                && COALESCE ( 'COD_SITUAC'[CH_ATIVO], "T" ) = "T"
                && 'COD_SITUAC'[DT_VENCIM] < TODAY ()
    )
)

Measure = --optimized
CALCULATE (
    SUM ( 'COD_SITUAC'[VL_SALDO] ),
    FILTER (
        'COD_SITUAC',
        NOT 'COD_SITUAC'[COD_SITUAC]
            IN { 2, 7, 8 }
                && 'COD_SITUAC'[CH_ATIVO] = "T"
                && 'COD_SITUAC'[DT_VENCIM] < TODAY ()
    )
)

[Pragati11]

HI @joaovb96 ,

Can you share some sample data here please?

I am assuming all columns in above SQL query are coming from same table.

You can easily convert this SQL query in DAX. Something like below:

totalAmount =
VAR defVal = "T"
VAR mainVal =
    SELECTEDVALUE ( yourtablename[CH_ATIVO] )
RETURN
    CALCULATE (
        SUM ( yourtablename[VL_SALDO] ),
        FILTER (
            yourtablename,
            NOT ( yourtablename[COD_SITUAC] )IN { '2', '7', '8' } 
            && COALESCE ( mainVal, defVal )
            && yourtablename[DT_VENCIM] < TODAY ()
        )
    )

 The above DAX is just written based on looking at your SQL query. I can't test it as I don't have access to your data.

Share some sample data or pbix file by removing any sensitive information so that I can test this DAX.

Thanks,

Pragati

[Greg_Deckler]

@joaovb96 Going to be something along the lines of:

Measure =
  CALCULATE(SUM('RECEBER'[VL_SALDO]),FILTER('RECEBER', [COD_SITUAC] NOT IN {2,7,8} && [DT_VENCIM] < TODAY() && [CH_ATIVO] = "T")

Usually better if you just post sample data and expected output. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.