cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rumeel2004
Frequent Visitor

Help with SQL subquery to DAX Please!

I am having difficulty translating what is a pretty simple SQL query with a subquery to DAX:

Select Distinct (SalesManKey)
From(

select max(a11.SalesManKey) SalesManKey from FactSales a11 join ( select a11.SalesManKey SalesManKey from FactSales a11 where a11.Active = 1 group by a11.SalesManKey, a11.WeekKey having sum(a11.SalesScore) >= 3.0 ) a1 where a11.Active = 1 group by a11.SalesManKey, a11.WeekKey
) b
2 REPLIES 2
Community Support
Community Support

Re: Help with SQL subquery to DAX Please!

Hi @rumeel2004 ,

 

You may create column like DAX below.

 

Column =
var d1=CALCULATE(SUM(FactSales[SalesManKey]),ALLEXCEPT(FactSales, FactSales[SalesManKey], FactSales[WeekKey]))
return
CALCULATE(MAX(FactSales[SalesManKey]),FILTER(ALLEXCEPT(FactSales, FactSales[SalesManKey], FactSales[WeekKey]), d1>=3&&FactSales[Active]=1))

Best Regards,

Amy

 

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

 

rumeel2004
Frequent Visitor

Re: Help with SQL subquery to DAX Please!

Sorry i updated the SQL query. I need to show Distinct Count in KPI Card.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors