Calculate tests per date per item

[Eyal]

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

[Eyal · Accepted solution]

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)

[amitchandak]

@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]

@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 items	date
step1	1	01/01/2021
step1	3	02/01/2021
step1	5	05/01/2021
step2	4	01/01/2021
step2	1	02/01/2021
step2	1	03/01/2021
step2	2	05/01/2021
step3	1	02/01/2021
step3	1	04/01/2021
step4	2	01/01/2021
step4	2	02/01/2021
step4	3	04/01/2021
Table B
Step	Test1	Test2	Test3	Test4
Step1	1	3		1
Step2				1
Step3		1	3	1
Step4	1		1

Thank you

Eyal