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.


     




     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

 

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

Anonymous   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.