cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
achude
Helper I
Helper I

add 2 measures to get only unique values

hi guys,

 

please i need you assistance, i have been trying to do this for a week now, i dont know if its my logic that is wrong. i have two measures as follows:

 

ActiveUsers = 
VAR s= CALCULATE(DISTINCTCOUNT(allproducts[CustomerId]),allproducts[TradeType] = 1, allproducts[TradeStatus]=0)
return
IF(ISBLANK(s),0,s)

 

 and 

 

 

EquityUsers = CALCULATE(DISTINCTCOUNT(allproducts[CustomerId]),allproducts[AggregatePurchaseValue] > 0)

 

 I would like to add these 2 measures and get the total number of distinct customers from the both of them. 

 

this is a copy of my dataset 

Link to Dataset 

 

thank you

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@achude , Please try a measure like

ActiveUsers =
VAR s= CALCULATE(DISTINCTCOUNT(allproducts[CustomerId]),filter(allproducts ,(allproducts[TradeType] = 1 && allproducts[TradeStatus]=0) || allproducts[AggregatePurchaseValue] > 0))
return
IF(ISBLANK(s),0,s)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
AlB
Super User III
Super User III

Hi @achude 

Do you want the intersect of the union of those two groups of customers?

Total users =
VAR activeUsers_ =
    CALCULATETABLE (
        DISTINCT ( allproducts[CustomerId] ),
        allproducts[TradeType] = 1,
        allproducts[TradeStatus] = 0
    )
VAR equityUsers_ =
    CALCULATETABLE (
        DISTINCT ( allproducts[CustomerId] ),
        allproducts[AggregatePurchaseValue] > 0
    )
RETURN
    COUNTROWS ( INTERSECT ( activeUsers_, equityUsers_ ) )

Use UNION instead of INTERSECT on the last line of code if that's what you need

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Thank you for your reply,

 

I tried both the union and the intersect, and they were not exactly what i wanted.

@amitchandak solution gave me the expected output.

amitchandak
Super User IV
Super User IV

@achude , Please try a measure like

ActiveUsers =
VAR s= CALCULATE(DISTINCTCOUNT(allproducts[CustomerId]),filter(allproducts ,(allproducts[TradeType] = 1 && allproducts[TradeStatus]=0) || allproducts[AggregatePurchaseValue] > 0))
return
IF(ISBLANK(s),0,s)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Thank you.

 

This is exactly what i wanted.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors