cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors