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

Microsoft Employee

## MY COUNTROWS DOESN"T SHOW TOTAL ROWS IN A TABLE

I need to find out the number of customers who latest active month == current month (selected)
So, here is my DAX, try to find out some one who active in current month && also has latest month == current month, my DAX can identify who are they now, but it won't show the total in the table

Lost customers =

VAR currentMonth = FIRSTDATE(KV_Cogsvc_Main[Month Year])

VAR latestRecord = CALCULATE(MAX(KV_Cogsvc_Main[Month Year]), ALLEXCEPT(KV_Cogsvc_Main, KV_Cogsvc_Main[Customer Name]))

VAR lostCustomersa = CALCULATETABLE(
VALUES(KV_Cogsvc_Main[Customer Name]),
FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=latestRecord))

VAR lostCustomersb = CALCULATETABLE(
VALUES(KV_Cogsvc_Main[Customer Name]),
FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=currentMonth))

VAR lostCustomersc = NATURALINNERJOIN(lostCustomersb, lostCustomersa)

RETURN COUNTROWS(lostCustomersc)
10 REPLIES 10
Community Support

Hi @garfieldhe ,

Have you resolved it? If not, you could use SUMX() to have a try.

https://docs.microsoft.com/en-us/dax/sumx-function-dax

``Measure = SUMX('your table',[Lost customers])``

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Variables are calculated in the context when they are defined, not when they're used, so unless you have this measure in a table with customer name, this won't work. You need to define your latest date within the context of a chosen customer.

Also, I think INTERSECT will work better here.

See below sample measure (note I haven't tested so may need some syntax fixing):

Lost customers =

VAR lostCustomersa = CALCULATETABLE(
VALUES(KV_Cogsvc_Main[Customer Name]),

VAR latestRecord = CALCULATE(MAX(KV_Cogsvc_Main[Month Year]), ALLEXCEPT(KV_Cogsvc_Main, KV_Cogsvc_Main[Customer Name]))
RETURN

FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=latestRecord))

VAR lostCustomersb = CALCULATETABLE(
VALUES(KV_Cogsvc_Main[Customer Name]),
VAR currentMonth = FIRSTDATE(KV_Cogsvc_Main[Month Year])
RETURN
FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=currentMonth))

VAR lostCustomersc = INTERSECT(lostCustomersb, lostCustomersa)

RETURN COUNTROWS(lostCustomersc)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Microsoft Employee

Thank you for the reply Allison, but the result still the same, the customers who met the requirements has been found, but the total number of them are not sum up in the table by month...

Super User
Can you please provide more info on how you are trying to use this measure and what is working and what isn't with screenshots or sample file with confidential info removed? How do you know the customers who met the requirements have been found? What is displayed instead of total?

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Microsoft Employee

Hi Allison,

This is what I trying to show

and here is the Formula I used for calculate the new customers number every month

New customers =

VAR currentCustomers = VALUES(KV_Cogsvc_Main[Customer Name])
VAR currentMonth = FIRSTDATE(KV_Cogsvc_Main[Month Year])

VAR pastCustomers = CALCULATETABLE(VALUES(KV_Cogsvc_Main[Customer Name]),
ALL(KV_Cogsvc_Main[RecordDate].[月份],KV_Cogsvc_Main[RecordDate].[MonthNo],KV_Cogsvc_Main[RecordDate].[年])
, KV_Cogsvc_Main[Month Year]<currentMonth)

VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

RETURN COUNTROWS(newCustomers)

Super User
Thanks, this helps a bit. What formula are you currently using for Lost Customers? Not sure why you have -1 for COUNTROWS function?

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Microsoft Employee

Hi Allison,

it is the formula you shared before, I just *-1 in COUNTROWS

Lost customers =

VAR lostCustomersa = CALCULATETABLE(
VALUES(KV_Cogsvc_Main[Customer Name]),
VAR latestRecord = CALCULATE(MAX(KV_Cogsvc_Main[Month Year]), ALLEXCEPT(KV_Cogsvc_Main, KV_Cogsvc_Main[Customer Name]))
RETURN
FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=latestRecord))

VAR lostCustomersb = CALCULATETABLE(
VALUES(KV_Cogsvc_Main[Customer Name]),
VAR currentMonth = FIRSTDATE(KV_Cogsvc_Main[Month Year])
RETURN
FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=currentMonth))

VAR lostCustomersc = INTERSECT(lostCustomersb, lostCustomersa)

RETURN COUNTROWS(lostCustomersc) * -1
Super User
Phew! I thought something strange was going on with the -1 for a second. 😛

Okay, so what's happening is that you are defining a 'Lost Customer' as someone who has the current month and last order date month as the same value. When you get to the total row, the current month value is no longer a single value.

You have currently used:
VAR currentMonth = FIRSTDATE(KV_Cogsvc_Main[Month Year])
to define your current month value, (I would prefer we use a date table ultimately, but won't matter for now).

In the total row, that is the first date that ANY customer ordered anything in the Main table. Adding a SUMX to your expression should work. I am assuming you want to do a count month by month, then sum those totals?

See if this works:
Lost customers =

VAR lostCustomersa = CALCULATETABLE(
VALUES(KV_Cogsvc_Main[Customer Name]),
VAR latestRecord = CALCULATE(MAX(KV_Cogsvc_Main[Month Year]), ALLEXCEPT(KV_Cogsvc_Main, KV_Cogsvc_Main[Customer Name]))
RETURN
FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=latestRecord))

VAR lostCustomersb = CALCULATETABLE(
VALUES(KV_Cogsvc_Main[Customer Name]),
VAR currentMonth = FIRSTDATE(KV_Cogsvc_Main[Month Year])
RETURN
FILTER(KV_Cogsvc_Main, KV_Cogsvc_Main[Month Year]=currentMonth))

VAR lostCustomersc = INTERSECT(lostCustomersb, lostCustomersa)

RETURN SUMX(VALUES(KV_Cogsvc_Main[Month Year]), COUNTROWS(lostCustomersc) * -1)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Microsoft Employee

thank you Allison, I have the same understanding, but somehow the lost customer column still not able to sum up the number of rows by month

I applied your latest solution, but the result is the same, I am selecting 2020-07, so total customers is 213, there are 31 customers who join on 2020-07, and some latest month won't last till latest 2020-08, but the number of lost customers still not able to be summed in the table.

Super User

@garfieldhe - This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.