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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors