Forum Discussion

fabiomanniti's avatar
fabiomanniti
Icon for Helper III rankHelper III
4 years ago
Solved

Count rows from a group by table with filter

I hope you can help me: I have two tables: a dimension and a fact table for users. I would like to find how many users have registered twice based on the VAT number. So I want to do a group by VAT...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    You could try

    Num duplicate customers =
VAR summaryTable =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( dim_users, dim_users[VAT] ),
            "@num rows", CALCULATE ( COUNTROWS ( dim_users ) )
        ),
        dim_users[is_last_snap] = TRUE ()
    )
RETURN
    COUNTROWS ( FILTER ( summaryTable, [@num rows] > 1 ) )
johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

You could try

Num duplicate customers =
VAR summaryTable =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( dim_users, dim_users[VAT] ),
            "@num rows", CALCULATE ( COUNTROWS ( dim_users ) )
        ),
        dim_users[is_last_snap] = TRUE ()
    )
RETURN
    COUNTROWS ( FILTER ( summaryTable, [@num rows] > 1 ) )
  • fabiomanniti's avatar
    fabiomanniti
    Icon for Helper III rankHelper III
    4 years ago

    This almost works...

    I don't know why I expect 86 results from the DB and I see 105 on PBI but I'm not sure it is a problem of the DAX Formula

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      4 years ago

      You could run the CALCULATETABLE part of the expression in DAX Studio, see if that gives any indication as to where the discrepancy lies

      • fabiomanniti's avatar
        fabiomanniti
        Icon for Helper III rankHelper III
        4 years ago

        Sorry, my bad... I wasn't realizing that the DB makes a difference between uppercase and lowercase and PBI doesn't. 
        So actually the PBI number was correct.

        Thank you very much