Forum Discussion
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...
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 ) )
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 ) )
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
You could run the CALCULATETABLE part of the expression in DAX Studio, see if that gives any indication as to where the discrepancy lies
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