How to calculate conditional customer distinct count?

[ravi_609]

Hello Friends,
I have attached data table below:

I need distinct count of CustomerID for "V1-V2" Common Vertical.
Note: If any customer have only "V1" Vertical and "V1-V2-V3"  then it will not count as V1-V2 Common Vertical.

Expected Output:
From dataset image only 2 customer in V1-V2 common group. so measure will give me 2 count.

Please help me out how to write dax for this type of condition.
Thank you once again in advance...!!!

[Ashish_Mathur]

Share data in a format that can be pasted in an MS Excel file.  Sharing an image is useless!!!!

[Fowmy]

@ravi_609 

Try this measure:

COUNTROWS(
	FILTER(
		ADDCOLUMNS(
				DISTINCT(Table06[CustomerID] ),			
				"V" ,
				VAR __A = COUNTROWS( CALCULATETABLE( VALUES( Table06[Vertical] ) , TREATAS( {"V1" ,"V2"} , Table06[Vertical] )) )
				VAR __B = COUNTROWS(CALCULATETABLE( VALUES( Table06[Vertical] ) ) )
				RETURN
				INT(AND(__A=2 ,__B=2))
		),
		[V] = 1
	)
)

[ravi_609]

@Fowmy Thanks for the response but This measure is not working...!!!


I got solution with SQL Server Query:
SELECT CustomerID,COUNT(Verical)
FROM Vertical
GROUP BY CustomerID
HAVING CustomerID not in (Select CustomerID From Vertical where Verical not in ('V1','V2')) and COUNT(Verical) = 2

But I am stuck here that how to use this same logic into measure.

[Fowmy · Accepted solution]

@ravi_609 

I tested withthe sample youprovided and it worked for me.can you explain how are youtrying to apply this measure?

My table:

The meaure and the result:

Porvide more details and screenshots of the error. You may also share dummy data that represent your actual data structure.

[Corey_M]

UniqueIDCount = 
COUNTROWS(
    FILTER(
        SUMMARIZE(
            YourTable,
            YourTable[ID],
            "Verticals", CONCATENATEX(VALUES(YourTable[vertical]), YourTable[vertical], ",")
        ),
        (CONTAINSSTRING([Verticals], "V1") && CONTAINSSTRING([Verticals], "V2")) 
        && NOT CONTAINSSTRING([Verticals], "V3")
    )
)

not the most eloquent solution, but this should work

[ravi_609]

@Corey_M   Thanks for the response but This measure is not working...!!!

I got solution with SQL Server Query:

SELECT CustomerID,COUNT(Verical)
FROM Vertical
GROUP BY CustomerID
HAVING CustomerID not in (Select CustomerID From Vertical where Verical not in ('V1','V2')) and COUNT(Verical) = 2

But I am stuck here that how to use this same logic into measure.