Forum Discussion

fabiomanniti's avatar
fabiomanniti
Helper 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
Super 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
Helper 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