Forum Discussion

ravi_609's avatar
ravi_609
Icon for Helper I rankHelper I
2 years ago
Solved

How to calculate conditional customer distinct count?

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 ...
Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    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.


     




     

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
2 years ago

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's avatar
ravi_609
Icon for Helper I rankHelper I
2 years ago

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's avatar
    Fowmy
    Icon for Super User rankSuper User
    2 years ago

    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.