Transform SQL TO DAX

[Anonymous]

Hi Guys, i need convert this SQL to DAX, but i can't do it. I tryed several DAX, but i found several errors.

SQL:

SELECT NFS,COUNT(CPF) AS PESSOAS FROM
	(
	SELECT CL.CPF AS CPF,COUNT(FT.NOTA_FISCAL) as NFS FROM FT_FATURAMENTO AS FT
	INNER JOIN DM_CLIENTE AS CL ON CL.SK_CLIENTE = FT.SK_CLIENTE

	GROUP BY CL.CPF
	)AS TEMP
GROUP BY NFS
ORDER BY 1

I need a result like this:

Number of clients who bought 1 turn -------1 client

Number of clients who bought 2 turn -------10 client

[Icey]

Hi @Anonymous ,

Could you share me some sample data and your expected result? Then I can test for you directly.

Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Icey

[amitchandak]

@Anonymous , You are looking for bucketing /binning. When a measure {COUNT(FT.NOTA_FISCAL) } is converted dimension using an independent table

refer these if, that can help you

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
https://youtu.be/CuczXPj0N-k

Create a measure of the count. Create a table with counts (Generateseries), Create a measure with CPF on the group by /values. Use = join with this new series table to get the correct value. 

Similar step is there for range in video, link 3