cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BernardoPedroso
New Member

Power BI- Convert SQL query to power bi

Basically i have to do on the power bi the same as the query that i do below.

 

selecT [Artigo] ,avg([uster]) AvgUster from (
          selecT *, Rank()
          over (Partition BY Artigo
          order by artigo asc, data desc) AS Rank_
FROM [Mundifios_DW].[dbo].[FACT_Laboratorios] b
where Fornecedor = '0013' --- Condições da query
) rs
where RANK_ <=5
group by [Artigo]
GO

I cannot understand the steps that i have to do to sucefully do that query on the power bi report that im doing. Can you please help me?

 

Best Regards

2 REPLIES 2
v-yulgu-msft
Microsoft
Microsoft

Hi @BernardoPedroso,

 

You can create a calculated table in Power BI with below similar DAX formula:

New table =
VAR TOP5table =
    TOPN (
        5,
        FILTER ( FACT_Laboratorios, FACT_Laboratorios[Fornecedor] = "0013" ),
        FACT_Laboratorios[artigo], ASC,
        FACT_Laboratorios[data], DESC
    )
RETURN
    GROUPBY (
        TOP5table,
        [Artigo],
        "AvgUster", AVERAGEX ( CURRENTGROUP (), [uster] )
    )

Reference: 

TOPN
Nested grouping using GROUPBY VS SUMMARIZE

 

For more advice, please refer to this link to provide necessary sample data and expected result which can achieved by above SQL code.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User IV
Super User IV

When you connect to your SQL database in the Query Editor, use "Advanced" and just paste in your SQL.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.