How to use a Measure on the X-as

[CBO2404]

Hello, 

I have a measure that counts the number of products. The number of products can be between 1 and 20 but in the future maybe more.
I can make a Visual Table with the number of products per customer.
What I would like is to show in a vusual of how many customers there are per number of products (see picture)

So 3 customers have 1 product; 6 customer has 2 products etc. No one has 4 products so i do not want to show this in my visual.

How is this possible? I could not add an extra field in my facttable to join.

the desired result

[Anonymous]

That's not that hard once you know exactly how PBI works. And the fact is it's a columnar relational data warehouse where you've got fact tables and dimensions. Fact tables are (should be) hidden and you slice via dimensions only. Keys should also be hidden not to pollute the space. Measures return about any aggregations you could imagine. And much more.

What you really need is another disconnected dimension where you're store your possible numbers of products, from 0, 1,... up to the number that's the biggest possible (20 in your case but this can be changed when you refresh the model or it can be calculated from the data you load). Once you have such a dimension, then you'll create a measure that will, for each such a number, count the number of customers that have this number of products. If somebody selects several such numbers from the dimensions, all you'll have to do will be sum up the measure's values for each such a number.

That's the mechanics behind it.

And here's how to do it in a well designed model:

[#Customers With N Prods] =
var ProdNumsSelected =
	// Num Of Prods is the
	// disconnected dimension
	DISTINCT( 'Num Of Prods'[# Products] )
var Result =
	SUMX(
		SUMMARIZE(
			FactTable,
			Customers[CustomerID]
		),
		int(
			[Number of Products] 
				in ProdNumsSelected
		)
	)
return
	if( Result, Result )

[CBO2404 · Accepted solution]

Thank you for your solution. But after many tries this works for me:

Number of Custumers with N products =
VAR mindate = MIN ( 'Dim_Calendar'[datum] )
VAR maxdate = MAX ( 'Dim_Calendar'[datum] )
VAR CustumerProductTable =
CALCULATETABLE (
SUMMARIZE (
'Fact_CustumerProduct',
Dim_Custumer[Custumer],
"no_products", DISTINCTCOUNT ( 'Fact_CustumerProduct'[prod_id] )
),
FILTER (VALUES ( 'Fact_CustumerProduct'[start_date] ), 'Fact_CustumerProduct'[start_date] <= maxdate),
FILTER (VALUES ( 'Fact_CustumerProduct'[end_date] ), OR ('Fact_CustumerProduct'[end_date] >= mindate, ISBLANK ( 'Fact_CustumerProduct'[end_date] )))
)
VAR Result =
SWITCH (
SELECTEDVALUE ( Dim_NoProducts[No_off_products] ),
"1", COUNTX (FILTER ( CustumerProductTable, [no_products] = 1 ), COUNTROWS ( CustumerProductTable )),
"2", COUNTX (FILTER ( CustumerProductTable, [no_products] = 2 ), COUNTROWS ( CustumerProductTable )),
"3", COUNTX (FILTER ( CustumerProductTable, [no_products] = 3 ), COUNTROWS ( CustumerProductTable )),
"4", COUNTX (FILTER ( CustumerProductTable, [no_products] = 4 ), COUNTROWS ( CustumerProductTable )),
"5", COUNTX (FILTER ( CustumerProductTable, [no_products] = 5 ), COUNTROWS ( CustumerProductTable )),
">5", COUNTX (FILTER ( CustumerProductTable, [no_products] >5 ), COUNTROWS ( CustumerProductTable ))
)
RETURN
Result

[Anonymous]

... apart from the fact that there is a huge problem with your solution: it's rigid and hardly maintainable. Can you see the simplicity of my measure compared to yours? Which one do you think will be faster on a big data set and which one do you think will be easier to understand when you come back to it after some time? Which one do you think will be easier to understand for the future dev that will have to maintain it? Also, my design is totally dynamic and can accommodate many different ways to group, slice and dice data (think about the future as well).

What's worse, your measure is not additive. That means if you select from the product number dimension several values at the same time, say, 2 and 3, your measure will return blank. Mine will correctly sum up the corresponding values. There are many other issues with your way of doing this... but it's up to you, obviously, if you want to solve the problem the right way in the best possible way or... want to later face problems and look for this very post in the sea of other posts.

Your choice 🙂