Convert SQL Consult to DAX

[Breno]

Hello,

I need help about how to convert this SQL query to DAX.

SELECT DISTINCT
R.NUMCAR,
R.CODCLIENTE,
COUNT(R.CODCLIENTE)OVER(PARTITION BY R.NUMCAR) AS QTDE_CLIENTE_POR_CARREGAMENTO,
COUNT(R.CODCLIENTE) AS CLIENTE_REPETIDO
FROM FATO_ROTINA8780 R
GROUP BY
R.NUMCAR,
R.CODCLIENTE
ORDER BY
R.NUMCAR,
R.CODCLIENTE

[smpa01 · Accepted solution]

@Breno  If you are querying a SSAS server

EVALUATE
SUMMARIZECOLUMNS (
    tbl[NUMCAR],
    tbl[CODCLIENTE],
    "QTDE_CLIENTE_POR_CARREGAMENTO", CALCULATE ( COUNT ( tbl[CODCLIENTE] ), ALLEXCEPT ( tbl, tbl[NUMCAR] ) ),
    "CLIENTE_REPETIDO", COUNT ( tbl[CODCLIENTE] )
)
ORDER BY tbl[NUMCAR], tbl[CODCLIENTE]

If not and simply building a visual, then use following Measures after dropping the axis (tbl[NUMCAR], tbl[CODCLIENTE]) on the viz

QTDE_CLIENTE_POR_CARREGAMENTO= CALCULATE ( COUNT ( tbl[CODCLIENTE] ), ALLEXCEPT ( tbl, tbl[NUMCAR] ) )

CLIENTE_REPETIDO = COUNT ( tbl[CODCLIENTE] )

[Breno]

@smpa01 thank you for your help....

With your idea I was able to solve the problem

[Greg_Deckler]

@Breno Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Breno]

Ok @Greg_Deckler I'll make better in the next post.