cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CBO2404
Frequent Visitor

How to use a Measure on the X-as

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.

 

CBO2404_1-1631710231573.png

the desired result

CBO2404_2-1631710505280.png

 

1 ACCEPTED SOLUTION
CBO2404
Frequent Visitor

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

View solution in original post

3 REPLIES 3
daxer
Solution Sage
Solution Sage

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
Frequent Visitor

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

View solution in original post

... 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 🙂

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!