Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

SQL query to DAX

Hi ALL I am trying to transform SQL query into DAX language but unable to do so. Please look at the query and help me with the DAX ************ SELECT CAST(CAST(Songs.SwedishCount AS DECIMAL) / CA...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Icey 

Thankyou very much for your help.  I tried it but it gives me Blank. I ran different variables separately and it runs, but as a whole it gives blank result.

 

I have added a Name column that was missing in "TrackData" table. and uploaded a new file which link is here

https://1drv.ms/u/s!AgrolwBG08XGjRgvhU_hIj17ZMwI?e=stn4Y1

 

I have also created two columns from last part of query which is 

TrackData'[Value] IN { "Ja", "SE" }

 But i am able to solve only half of query with this method.   You can give it a try if you can. or I will continue to work on this with the help of your query.. 

THanks

Icey's avatar
Icey
Community Support
6 years ago

Hi Anonymous ,

 

Based on my test, the cause is that there are no records that satisfy the conditions at the same time.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thankyou very much for double check

    I will try to load more rows to check if there's record, because there are more than 2 million records and i only loaded half million, 

    Can you suggest me how to think like this as you did in your solution. ? Thought process ? because i was not able to think like this 

    any course or suggestions to improve thinking about how to build DAX from SQL query.

     

    • Icey's avatar
      Icey
      Community Support
      6 years ago

      Hi Anonymous ,

       

      Sorry to reply late.

      It is necessary to understand the calculation logic of SQL query firstly. And, some DAX functions are needed to understand. Then, convert it to DAX according to the calculation logic.

       

      In DAX, "VAR" stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions.

      Reference: DAX: Use variables to improve your formulas.

       

      Please let me know if the expressions above work.

       

       

      Best regards

      Icey

       

      If this post helps,then please consider accepting it as the solution to help other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Icey 

        Its fine with delay, as i am working another project side by side. anyway, I tried your solution separately like each part of long DAX and it works but when i run whole code it gives me blank .  SQL code run on sql server gives data , I am unable to know at this point about why it is not giving me data. 

         

        I tried to remove date restriction in VAR P  **&& 'Episode'[Datum] >= DATE ( 2020; 3; 9 )
                                                             ** && 'Episode'[Datum] <= DATE ( 2020; 3; 16 )

        but result was the same BLANK.  I even increase the data load to 1m rows,  what do  you think what is the problem.?