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) ) 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?
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.
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:
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)
@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.
Check out the News & Announcements to learn more.
Click here to read more about the October 2021 Release!
Check out the new Power Platform Community Connections gallery!
Mark your calendars and join us for our next Power BI Dev Camp!