Power BI DAX Formula for a measured value

Hi,

I am quite new to Power Bi

The Following Query in SQL will give me the most popular item based on the highest OrderQuantity.

How would I go about producing the same thing as a new measure in Power BI

Select top(1)
Description as [Most Popular Item]
from
(
SELECT
[ItemsId]
,Description
,Sum(OrderQuantity) as [Quantity Sold]

FROM [Data_Analysis].[dbo].[dimFactTable] as F

left join [Data_Analysis].[dbo].[dimItems] as I

on
f.ItemsId=i.Items_Id

group by [ItemsId] ,Description

) as tmp

order by [Quantity Sold] desc

Hi there. First you should check you SQL because it has sintax errors. One thing you need to know about dax is that joining is not a regular operation. Why? beacause you have relationships on your tabular model that will execute joins depending on how you show the data in power bi visualization.

Then I ask, what do you need? a table, measure or column?

IF you have related the FactTable and Items by id, then try this measure to get the first description. This is only to replicate your query, I don't  think this is the best way to know the first description with most Quantity sold. You can get this on a visualization with description as categorical and sum orderquantity order by desc having advanced filter with topn in 1.

Most Popular Item =
MAXX (
    SUMMARIZE (
        dimFactTable,
        dimFactTable[itemsId],
        dimFactTable[Description],
        "Quantity Sold", SUM ( dimFactTable[OrderQuantity] )
    ) ORDER BY [Quantity Sold] DESC,
    Description
)

I have my doubts about that Order By. Let me know if it works. Otherwise we can try ADDCOLUMNS to get the sumarization.

Regards, hope this works