Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User

@Anonymous , 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)

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

Hi @Anonymous 

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

Anonymous
Not applicable

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
Super User

@Anonymous , 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)

Anonymous
Not applicable

Thank you.

 

This is exactly what i wanted.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.