Forum Discussion

joaovb96's avatar
joaovb96
Regular Visitor
4 years ago
Solved

Convert SQL to Power BI

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_da...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    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 ()
    )
)

     

     

joaovb96's avatar
joaovb96
Regular Visitor
4 years ago

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's avatar
smpa01
Community Champion
4 years ago

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 ()
    )
)