cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## SUM total at the end when using If( not( ISBLANK)

Hello,

I have this formula below that counts the # of Customer's ID that had revenue >\$0 monthly. It works great.

``````BP #T4Bs = COUNTROWS(
FILTER(
SUMMARIZE(
CALCULATETABLE(
VALUES('dContact List'[Access Number]),
DATESBETWEEN(
dCalendar[calendar Date],
EDATE(MIN(dCalendar[calendar Date]),0),
MAX(dCalendar[Calendar Date])
)
),
'dContact List'[Access Number],
"Metric",CALCULATE(
[BP Trxs Fees],
DATESBETWEEN(
dCalendar[calendar Date],
EDATE(MIN(dCalendar[calendar Date]),0),
MAX(dCalendar[calendar Date])
)
)
),
[Metric]>.1
)
)``````

I have another dax that does the same for another service. the measure is called [PR # T4Bs].

Now, I need to create a measure that in case [BP # T4Bs] =1 and [PR # T4Bs]=1, I want them to count the Customer ID for each month.
I created this Dax, but total is 1 at the end. I need the logic to count how many Customers IDs has both measures =1.

``````Combo Users = (if(not(ISBLANK([BP #T4Bs]))
&& not(ISBLANK([PR #T4Bs])),
1,BLANK()))``````

Any help would be much appreciated.

I need the total to sum Total based on customersID

Thanks,

1 ACCEPTED SOLUTION
Community Support

You can try the following methods.

``````Combo Users =
IF (
HASONEVALUE ( 'Table'[ID] ),
IF (
NOT ( ISBLANK ( [BP # T4Bs] ) ) && NOT ( ISBLANK ( [PR # T4Bs] ) ),
1,
BLANK ()
),
CALCULATE (
COUNT ( 'Table'[ID] ),
FILTER ( ALL ( 'Table' ), [BP # T4Bs] = 1 && [PR # T4Bs] = 1 )
)
)``````

The function IF (HASONEVALUE ()) controls the output of Total.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Helper I

thank you so much, worked perfectly!

Community Support

You can try the following methods.

``````Combo Users =
IF (
HASONEVALUE ( 'Table'[ID] ),
IF (
NOT ( ISBLANK ( [BP # T4Bs] ) ) && NOT ( ISBLANK ( [PR # T4Bs] ) ),
1,
BLANK ()
),
CALCULATE (
COUNT ( 'Table'[ID] ),
FILTER ( ALL ( 'Table' ), [BP # T4Bs] = 1 && [PR # T4Bs] = 1 )
)
)``````

The function IF (HASONEVALUE ()) controls the output of Total.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors