Forum Discussion

Breno's avatar
Breno
New Member
3 years ago
Solved

Convert SQL Consult to DAX

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, COU...
  • smpa01's avatar
    smpa01
    3 years ago

    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] )

     

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
Breno
New Member
3 years ago

smpa01 thank you for your help....

With your idea I was able to solve the problem