Forum Discussion

jaimemagana's avatar
jaimemagana
New Member
6 years ago

DAX table - CRM

Hi there,

 

I´m pretty new to Power Bi, so bare with me for  minute. I wnat to analyze my client portfolio and detect sales opportinuties. For that I need to undestand my customer behaviour. I want to create a table with DAX to do that.

I have a set of tables:

  • Calendar
  • Clientes
  • Sales
  • Repairs

I want to create a DAX Table to analyze CRM: I want to have the following columns:

 

  • Year
  • Client ID (from Clients Table)
  • # of cars sold to that client in that year
  • # os car repars undergone in that year

 

I tried the following:

 

TableCRM =
row(
"Year", Calendar[year],
"Client", Clients[clientid],
"Cars Bought", calculate(count(carsales[saleID]), userelationship(calendar[date], carsales[date])),
"Repairs", calclate(calculate(count(repairs[repairID]), userelationship(calendar[date], repairs[date]))
 
I am missing somethig, because it crashes...
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    jaimemagana , try like

    TableCRM =
    summarize(carsales,
    Calendar[year],
    Clients[clientid],
    "Cars Bought", calculate(count(carsales[saleID]), userelationship(calendar[date], carsales[date])),
    "Repairs", calculate(count(repairs[repairID]), userelationship(calendar[date], repairs[date]))
    )

    • jaimemagana's avatar
      jaimemagana
      New Member
      6 years ago

      First, and foremost, thanks your replying.

       

       

      Almost, but not quite. But you've given me the righ track to seek. With your code the Year colum does not work. Also, I have to set the main table to summarize to Clients, by Client ID. Then I am able to calculate the measures "Repairs" and "Cars Bought"

       

      TableCRM =
      summarize(
      Clients,Clients[idClient],
      "Cars Bought", calculate(count('Sales'[Saleid]), userelationship(Calendar[Date], 'sales'[Date])),
      "Repairs", calculate(count(Repairs[idRepair]), userelationship(Calendar[Date], Repairs[Date]))
      )
       
      Remmember that Sales, Clients and Repairs are separate Tables. Since I did not post the relationships and tables of the model befre, I´ll do it now.
       
       
      So, the code from above is actually:

      TableCRM =
      summarize(
      Clients,Clients[GCLI-idclie],
      "Cars Bought", calculate(count('Sales'[BASTIDOR]), userelationship(Calendar[Date], 'Sales'[MATRICULA_1])),
      "Repairs", calculate(count(Repairs[TOCA-numors]), userelationship(Calendar[Date], Repairs[TOCA-feccie]))
      )
       
      What I am missing is the Year field (Año) in the Calendar table.
       
      Does this help?