Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Product combination per customer

Hi guys


I'm struggling here, any help is welcome!
I'm looking to generate a calculated column for each costumer in tabel Dim_Klant that tells me which products are in his contract at the end of any period. These are rental contracts, so the time aspect is important for my visual. I want to track how the product combinations developed over time.

I've created a table for each product that was active at which date.. that's the middle table named [historisch actief]. It's linked to my calendar table by each date that a product in a contract was active.



So now im trying to get a list per costumer in Dim_Klant of the active products that he was renting at the end of any period. I've tried this formula but it says i don't have enough internal memory to perform the calculation.



PMC = 

VAR tabelproducten =
    CALCULATETABLE( VALUES( Dim_Product[Family] ) ,
    FILTER( Fact_CR_historisch_actief, [Contractwaarde einde periode] > 0)
    ", ",




The "contractwaarde einde periode" measure is as follows:




Contractwaarde einde periode = 

var __maxPeriod = MAXX( VALUES( Kalalalender[Date] ) , Kalalalender[Date] )

var resultaat = 
    SUM( Fact_CR_historisch_actief[Aantal_Prijs__c] ),
    Kalalalender[Date] = __maxPeriod

IF( resultaat = 0,0,resultaat)




Is there any other way to do this so my PC has enough memory, or what should i do?


Frequent Visitor

@selimovd can you help me out with this one?

Hey @Bertverderrie ,


it would be easier if you could share the file. Is that possible?


Be aware when you do this as a calculated column, the values are fixed. So calculated columns won't change when you change a slicer. As the table Dim_Klant doesn't contain any date rows, this solution with the calculated column in Dim_Klant won't work.


So I think you should in the report create a matrix with the customer and date on the rows and as a measure you count the amount of data available.

Something like this:

myMeasure =
    COUNTROWS( Fact_CR_historisch_actief ),
    Fact_CR_historisch_actief[Contractwaarde einde periode] > 0


But again, I can just guess as I don't see the data model and also the result is not totally clear for me.


If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards


Did you manage to crack it?

Hey @Bertverderrie ,


I don't really see why you want to do a measure or calculated column.

You can solve that with slicers and a matrix.


You can filter for the date, product family and see which customer has which amount of specific products at the given time:



If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards


Hi @selimovd ,

Maybe I can show you why,
I've added the desired column to the sample excel..
I want to track the amount of clients that were under contract with a certain combination of products at any time. In order to get a visual looking something like this

Sample file here 


(im aware this visual shows only the number of products, not unique customers but you get the idea)

@Bertverderrie  "sorry, this download link no longer exists :("

@selimovd thanks for the quick response!


I've replicated a sample dataset for the relevant tables. Is this enough for you? 

I do need a column because i need to track the amount of clients that had a certain combination of products at any given period. I do see now why i cannot do that in the Dim_Klant table because it has no dates, thanks for that! I've tried to put the column in table Historisch_actief but i can't seem to get it to work..

The desired result in this sample is that i can put together a visual that says that on january 4th, 1 customer had active contracts for all 3 product families, and 2 customers had active contracts for only Furniture.

Helpful resources

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

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!