cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bertverderrie
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.

Bertverderrie_0-1623831864346.png

 

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)
    )
  
Return
CONCATENATEX(
    tabelproducten,
    Dim_Product[Family],
    ", ",
    Dim_Product[Family],
    ASC
)

 

 

 

The "contractwaarde einde periode" measure is as follows:

 

 

 

Contractwaarde einde periode = 

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

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

return
IF( resultaat = 0,0,resultaat)

 

 

 

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

 

7 REPLIES 7
Bertverderrie
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 =
CALCULATE(
    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
Denis
 

@selimovd 

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:

selimovd_0-1624347533924.png

 

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
Denis
 

 

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 

 

Bertverderrie_0-1624347792366.png
(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? https://justbeamit.com/8rpg4 


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

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors