Count rows from a group by table with filter

[fabiomanniti]

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 number, filter rows that have a count(distinct id)>1 and then count how many rows do I have.

Furthermore there is one more filter I should consider: since it is a dimension table, I must take only the last snap.

So

1. Get last snap from fact_users (is_last_snap = true)
2. Get rows with duplicates (count(distinct id)>1)
3. Count how many rows do I have

So, my output is supposed to be a simple number.

I know how to do a GROUP BY table with DAX but I don't know how to put filter in it.

GroupedByCF = 
GROUPBY(
 dim_users,
 dim_users[VAT],
 "RowCount",
 COUNTX(
 CURRENTGROUP(), 
dim_users[id]
 )
)

This could potentially work (I can also put filter "RowCount>1" into the next measure but I don't know how to put the last snap (since it comes from another table). 

So my Idea was to create a table like this and then to create a measure to count rows.

If it was a SQL I would do something like this

WITH CTE AS (
    SELECT d.vat as vat,
           count(distinct d.id) as counting
    FROM fact.users f
    LEFT JOIN dim.users d ON f.id_tk = d._tk
    WHERE 1 = 1
    AND f.d_date_tk = get_last_msnap_date()
    GROUP BY d.vat
    HAVING count(distinct d.id) > 1
)
SELECT COUNT(*)
FROM CTE

[tamerj1]

Hi @fabiomanniti 
Please try

GroupedByCF =
COUNTROWS (
    FILTER (
        GROUPBY (
            FILTER ( dim_users, dim_users[is_last_snap] = TRUE () ),
            dim_users[VAT],
            "RowCount", COUNTX ( CURRENTGROUP (), dim_users[id] )
        ),
        [RowCount] > 1
    )
)

[johnt75 · Accepted solution]

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]

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]

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]

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