Power BI DAX Formula for a measured value

[fahim_ash123]

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

[fahim_ash123]

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

[rafaelmpsantos]

Look for TOPN function.

[ibarrau]

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

[fahim_ash123]

Hi,

Thanks for the reply

What is wrong with my SQL Syntax? 

It seems to execute prefectley in SSMS 

Your DAX Formula Doesn't seems to be working with the order by caluse