Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Creating Measure column in a different table

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 i...
Need Help
Tutorial Requests
v-danhe-msft's avatar
v-danhe-msft
Microsoft Employee
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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