Forum Discussion

Cobra77's avatar
Cobra77
Post Patron
7 years ago
Solved

Filter in variable KO

Hi

We wants to know the number of duplicates 

 

Nb Duplicates =
VAR tabgrp =
SUMMARIZECOLUMNS (
'Client'[Nom];
Client[Prénom];
Client[Date_naissance];
"nb"; DISTINCTCOUNT ( Client[Id_Origin] ) )

VAR f = FILTER(tabgrp;[nb]> 1)

RETURN CALCULATE(COUNTROWS(tabgrp);f)

 

But f = FILTER(tabgrp;[nb]> 1)  don t work

 

Thanks for your help.

Need Help
Show and Tell
  • AlB's avatar
    AlB
    7 years ago

    Hi Cobra77 ,

    Try this small modification. Bear in mind that variables in DAX are immutable once they've been assigned a value at declaration. Therefore, tabgrp will not be affected by the filter argument in CALCULATE(COUNTROWS(tabgrp); f)

    Nb Duplicates =
VAR tabgrp =
    SUMMARIZECOLUMNS (
        'Client'[Nom];
        Client[Prénom];
        Client[Date_naissance];
        "nb"; DISTINCTCOUNT ( Client[Id_Origin] )
    )
VAR f =
    FILTER ( tabgrp; [nb] > 1 )
RETURN
    COUNTROWS ( f )

     

1 Reply

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi Cobra77 ,

    Try this small modification. Bear in mind that variables in DAX are immutable once they've been assigned a value at declaration. Therefore, tabgrp will not be affected by the filter argument in CALCULATE(COUNTROWS(tabgrp); f)

    Nb Duplicates =
VAR tabgrp =
    SUMMARIZECOLUMNS (
        'Client'[Nom];
        Client[Prénom];
        Client[Date_naissance];
        "nb"; DISTINCTCOUNT ( Client[Id_Origin] )
    )
VAR f =
    FILTER ( tabgrp; [nb] > 1 )
RETURN
    COUNTROWS ( f )