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

     

     

Pragati11's avatar
Pragati11
Super User
4 years ago

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