Help with SQL subquery to DAX Please!

[rumeel2004]

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

[v-xicai]

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]

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