topic Re: sql to DAX in DAX Commands and Tips

sql to DAX

Anonymous — Tue, 10 Sep 2019 07:44:11 GMT

Hi,
Can you please let me know if the following sql should give the same result as the followed DAX?
Thank you

select
p.[field1], count(distinct p.[field3]) AS "Mycount"
from
TableP AS p
INNER JOIN TableH AS h ON p.ID = h.ID
where
h.[field7] > 0
AND p.[field2] = 'some text'
group by
p.[field1]
having count(distinct p.[field3]) >= 2
--------------------------------------------------------
FILTER (
SUMMARIZE (
'TableP'
,'TableP'[field1],
'TableP'[field2],
"Mycount", DISTINCTCOUNT ( 'TableP'[field3] ),
),
[price] > 0
&& SEARCH ( "some text", 'TableP'[field2], 1, 0 )
&& not ISBLANK('TableP'[field3])
&& [MyCount] >= 2
)

Re: sql to DAX

Anonymous — Tue, 10 Sep 2019 16:24:23 GMT

Try to use below DAX. Just replace your table and column names.

You can add more filters too.

FILTER( CALCULATETABLE( SUMMARIZECOLUMNS(DimProduct[EnglishProductName], 
		DimProduct, 
		"Mycount", 
		DISTINCTCOUNT( FactInternetSales[OrderDate]) 
		) 
	),
	CALCULATE(DISTINCTCOUNT( FactInternetSales[OrderDate])) >= 3 &&
	DimProduct[EnglishProductName]="some text"
)

Thanks.

Re: sql to DAX

Anonymous — Wed, 11 Sep 2019 11:27:13 GMT

Hi,
This seems to be giving the similar result to my filter and summarize dax

Re: sql to DAX

Anonymous — Wed, 11 Sep 2019 11:36:32 GMT

You can go ahead with anything as long as it's giving the correct result.. :)

Thanks.