Forum Discussion

Eyal's avatar
Eyal
Icon for Helper II rankHelper II
5 years ago
Solved

Calculate tests per date per item

Hi

 

I am strugeling with the following,

I have two tables. 
Table A - a claculated table (so cant use power query) - get the number of item produced in each step by date

Table B- is a key table that indicate what and how many test ate done on each step.

 

I want to generate a table that will show how many test per test type are preformed every day.

here are the diffrent tables. I could easily creat the result with power query, but since table A is a claculated table in my scenario, i need a dax based solution.

Thank you 

for all your help

 

 

 

 

Need Help
  • Eyal's avatar
    Eyal
    5 years ago

    Dear all 

     

    I was able to find a solution.

    hope it helps someone 

     

    step 1 :

    creat a new table based on summerization  of the Table A mentioned above

    Plan =
    SUMMARIZE(
    FILTER('TableA',NOT('TableA'[Activity] IN { "Not relevent item 1", "N/R", ......})),
    'Calendar'[Year#],
    'Calendar'[Month#],
    'Calendar'[MonthName],
    'TableA'[project],
    'TableA'[Activity],
    'TableA'[BR Scale],
    "Items",sum('TableA'[Run])
    )

     

    Step 2 make a relationship between new table and table B based on the "Step"

     

    for each test a calculated column

    # of test = 

    var test = LOOKUPVALUE(TableB[Test1],TableB[Step],'plan'[Step])
    return
    ('Plan'[Items]*test)

     

     

3 Replies

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

    Eyal , if Table A is Table; you have as the source, I think you should better reconsider the source format. If not please share the sample source data for A and B in text format

    • Eyal's avatar
      Eyal
      Icon for Helper II rankHelper II
      5 years ago

      amitchandak 

      I am not sure I follow.

      Table A in my real life scenario is a claculated table.

      It is generated by :

       

      SELECTCOLUMNS(
      GENERATE(
      '1-Task Pool',
      DATESBETWEEN(
      'Calendar'[Date],
      '1-Task Pool'[StartDate],
      '1-Task Pool'[DueDate]
      )
      ),
      "Date",'Calendar'[date],
      "project", '1-Task Pool'[Project],
      "Milestone",'1-Task Pool'[Milestone],
      "Task",'1-Task Pool'[Task],
      "Sub-Task",'1-Task Pool'[Sub-task],
      "FTE",'1-Task Pool'[FTE],
      "ID",'1-Task Pool'[Id],
      "Run",'1-Task Pool'[% Run],
      "Activity",'1-Task Pool'[Activity Type]
      )
       
      The 
      "Run",'1-Task Pool'[% Run], => result in the number of products
      "Activity",'1-Task Pool'[Activity Type] => The step
       
      Table B is exactly as descibed above. 

       Tha data i generated as an example
      Table A    
      Step# of itemsdate  
      step1101/01/2021  
      step1302/01/2021  
      step1505/01/2021  
      step2401/01/2021  
      step2102/01/2021  
      step2103/01/2021  
      step2205/01/2021  
      step3102/01/2021  
      step3104/01/2021  
      step4201/01/2021  
      step4202/01/2021  
      step4304/01/2021  
           
           
      Table B    
      StepTest1Test2Test3Test4
      Step113 1
      Step2   1
      Step3 131
      Step41 1 

       

      Thank you

      Eyal

  • Eyal's avatar
    Eyal
    Icon for Helper II rankHelper II
    5 years ago

    Dear all 

     

    I was able to find a solution.

    hope it helps someone 

     

    step 1 :

    creat a new table based on summerization  of the Table A mentioned above

    Plan =
    SUMMARIZE(
    FILTER('TableA',NOT('TableA'[Activity] IN { "Not relevent item 1", "N/R", ......})),
    'Calendar'[Year#],
    'Calendar'[Month#],
    'Calendar'[MonthName],
    'TableA'[project],
    'TableA'[Activity],
    'TableA'[BR Scale],
    "Items",sum('TableA'[Run])
    )

     

    Step 2 make a relationship between new table and table B based on the "Step"

     

    for each test a calculated column

    # of test = 

    var test = LOOKUPVALUE(TableB[Test1],TableB[Step],'plan'[Step])
    return
    ('Plan'[Items]*test)