Filter out customers who don't buy certain products

[robH]

Hi,
I have an excel spreadsheet that pulls in my customer details and the products I buy. I have a measure below that puts them into Income Groups. I would like to add a row in my grouping for customers who don’t buy the products I am looking at. These customers don’t therefore have a 0 value associated them it’s just they have no values at all!

This is to help me see how new product launches are going and who isn’t buying certain products.

My measure which works is:
Customer Sales by Sales Group £ =
(calculate ([Total £ Sold],
FILTER(values(‘Stoves’[Name] ),
COUNTROWS(
FILTER(‘Income Groups’,
[Total £ Sold] > ‘Income Groups’[Min] && [Total £ Sold] <= ‘Income Groups’[Max] ) )
>0 ) )
and the table results are:

Many Thanks
Rob

[BrianJ]

@robH,

In your table visual, have you tried turning on the option of “show items with no data”?

https://youtu.be/0soi-uQTNb0

Without a PBIX to test this, I can’t say for certain, but I suspect this may be an easy way to accomplish what you want to do.

• Brian

[robH]

Brian, yes I have - however I want to have an income group that counts the customers who have no data rather than exclude completely.
Rob

[BrianJ]

@robH,

OK, I get it. I am sure this is doable, but I think it’s going to be a relatively heavy lift because counting blank rows is a fundamentally different calculation than the other iterations over your Income Groups table.

The approach that comes to mind is based on the solution @Nick_M developed to address the “Top N and Others” question:

Top N and an Others bucket

i’ve worked on something like this before. It can be done, but it’s not as easy as you would think. Here’s the outline of what I have done. There are notes in the code, but hopefully will be able to follow along: /*These measure will take the TopN of a dimension ( City in this case) and return the top N individual valuesl //along with a new row called "Remaining" with the remaing total //Then a rankx measure will sort the cities from highest to lowest with the Remaining row always at the botto…

I think the outline of the general approach would be:

1). Using your current measure, replicate the info in your matrix visual virtually as a table variable

2. Develop a measure to count your blank rows, probably using COUNTBLANK, or COUNTROWS(ALL) minus COUNTROWS(ALLNOBLANKROW)

3. Using ROW, build a row incorporating the info from 2) and matching the structure of your virtual table in 1).

4. As another variable in the same measure as 1), append the row created in 3) to the table in 1) using UNION

5. write the DAX to output the relevant info from your complete virtual table in 4) to your report visuals as the RETURN statement in the measure created in 1) and 4)

As I said, doable but not easy… (unless someone has figured out a way to iterate over blank rows in a parameter table that I’m unaware of).

@Nick_M’s solution above is well-commented, so should provide a nice adaptable framework if you decide this is a road you want to go down. And the forum is always here to help…

• Brian

[BrianJ]

@robH,

In thinking a bit more about this, what makes the approach above difficult is combining apples and oranges calculations into the same table/matrix visual. If you were willing to present the non-purchasers’ (i.e., blank rows) info in a separate visual, that probably cuts the degree of difficulty by a factor of ten.

• Brian

[robH]

Brian,
Im happy for a new visual to show those customers who haven’t purchased, and I think that will be the only way to do it, as essentially there is no data to look. The ‘Blanks’ only appear after pulling into a table visual for instance.

Regards
Rob

[BrianJ]

@robH,

There’s a lot you can do within measures re: counting and filtering on blanks. Here’s a good video that addresses some of the functions you’ll probably find useful for this, including COUNTBLANK, COUNTA and COUNTAX:

And this is a very good overview of how DAX handles blanks, nulls and zeros:

Hope this is helpful.

• Brian