Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

how to make a condition data column to equal decode date in oracle

Hi,

 

I have issue with my query to not let user enter a input value using a parameter a web browser in power bi server, because this not value in power yet

 

so so sad .

 

in this time i changed a lot of my old code to get this one

I am looking for a hit how to pass Data_g To not from only TO using a Colum in table with decode)

DECODE (c.paytyp, 1, c.CHKDAT_G, 2, c.PAYDAT_G) >'31/12/2018')

like in the pic

SELECT   

         Data_g,

 

         (SUM (NVL (actlon, 0)) + SUM (NVL (fees_paid, 0))

        - SUM (

             NVL (

                (SELECT  SUM (NVL (FEES_PAID, 0))

                   FROM loan50 c

                  WHERE     a.brnoff = c.brnoff

                        AND a.cntno = c.cntno

                        AND c.payflg = 1

                        AND 

                               DECODE (c.paytyp,  1, c.CHKDAT_G,  2, c.PAYDAT_G) >'31/12/2018'

                               ),

                0)))

         Sum_pay

  FROM lon1001 a

 WHERE     a.brnoff = f.brnoff(+)

       AND a.cntno = f.cntno(+)

       AND  a.brnoff = 0100 

       AND Data_g BETWEEN '01/01/2018' AND '31/12/2018'

       GROUP BY  

         Data_g,

 

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support

    Hi Anonymous ,

     

    Sorry for our late reply, but the calculated columns are computed during the database processing and then stored in the model. So In your scenario, we cannot make the calculated column dynamically by the value of slicer.

     

    But we can use the measure to calcule the sum usch as following:

     

    Measure = Calculate(Sum('Table'[Value]),Filter('Table',Switch([c.paytyp],  1, [c.CHKDAT_G],  2, [c.PAYDAT_G]) >Max('Calendar'[Date])

     

    If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    Community Support

    Hi Anonymous ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,