Forum Discussion

Bertverderrie's avatar
Bertverderrie
Frequent Visitor
5 years ago

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 ...
Need Help
  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    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
     
    • Bertverderrie's avatar
      Bertverderrie
      Frequent Visitor
      5 years ago

      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.

      • selimovd's avatar
        selimovd
        Icon for Most Valuable Professional rankMost Valuable Professional
        5 years ago

        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
        Denis