Creating Measure column in a different table

[Anonymous]

Hi Everyone !

I am struggeleing to creat a measure column in a different table!  I created a table to read data from all other tables based on the list of work order codes & dates which are common in each seperate table. I creat a matrix table which it's data can change based on start and finish date filter as per below :

Main issue is that I have one table for assets. all assets have daily rate, except assets for one work order . All assets of that work order  have  monthly rate ,costs company  $1000 per month if we use each any of them , no matter 1 day or 30 days per month.

To be able to count number of month we used each asset ( for instance: 3T Forklift) I created below column:

Month = format('MRMS Asset '[Date-Asset],"MMMM")

then below measure column:

Asset Monthly cost = calculate(distinctcount('MRMS Asset'[Month])*1000, filter('MRMS Asset ','MRMS Asset '[Asset]="3T Forklift "&&'MRMS Asset '[WorkOrderCode]="CW - PO 4800058329"))

in the last step, I tried to link the above monthly cost to first table to see the data in the matrix table and no luck so far !

I really appreciate if anyone can help me here.

[v-danhe-msft]

Hi @Anonymous,

Based on my test, the measure could work on my side and I could not figure out "a different table" means which table? In your problem, it seemed you are usiing the MRMS Asset table and your measure are not related to other table, could you please offer me more information and post me your desired result?

Also you can test with our sample report on your side to see if the same issue occurs.

https://www.dropbox.com/s/6gfxsa77olyzkds/Creating%20Measure%20column%20in%20a%20different%20table.pbix?dl=0

Regards,

Daniel He

[Anonymous]

Hi@v-danhe-msft

I have 3 fidderent tables named : employee table, Asset table, invoice table and they are common in work order codes.

 I united all work order codes in a new table (WO)

  name= CROSSJOIN(calendar(date(2017,06,01),date(2019,12,31)),SUMMARIZE(WO,WO[WorkOrderCode]))

I created a new table (calculation Table) 

2- I added 2  calculated column in calculation table, to sum direct hours and indirect hours for wach work oder from employee table

3- I adedd another calculated column to sum asset hours(P&E) from Asse table

by changing the date in date between filter, numbers would change

 

Now, the issue is that some of equipment in below asset table have monthly rate instead of daily.

To measure their cost I went through below steps:

1- to count the number of mount we used that asset, I created a new column 

        Month = format('Asset '[Date-Asset],"MMMM")

2- then I added a measure column

Asset Monthly cost = calculate(distinctcount(' Asset'[Month])*1000, filter('Asset ',' Asset '[Asset]="3T Forklift "&&' Asset '[WorkOrderCode]="CW - PO 4800058329"))

the result is $8000 which is correct.

Now, the issue is that I can not add the asset monthly cost to the above matrix ! Or in other words, I can not read asset monthly cost  in calculated table. I tried to add two above columns directly in calculate table, but when I add it into the matrix table, the result is wrong and wont change by playing with date betweeh filter.

Hope I explained the situation clearly 

[Anonymous]

no solution?