Forum Discussion

duisinvalid's avatar
duisinvalid
Regular Visitor
1 year ago
Solved

Creating a Measure Between Two Unrelated Tables

I have an issue in such that I want to calculate the total hours that each of machine type is running
across all factories. I have snippet a small dataset of the two tables that I have been working on.

 

Machine_Type Factory Population
X A 1
Y A 5
Z A 4
X B 6
Y B 3

 

Factory Day_Unopen
A 10/7/2024
B 10/8/2024
A 10/9/2024
B 10/10/2024

A 10/11/2024

 

I want to accomplish where I can say number of hours active for in a time period for each machine
(assuming its 24 hours for every day that it’s not open). I already have Day Unopen have a relationship
with a datetable.


What I have so far is a measure on table B that says count(datetable.[date]) * 24 – count
(tableB.[DayUnopen]) * 24. This works on getting the count of the hours across the different factories. Now I
want to get the total hours across all factories.


Using the snippet of the data above, Machine X should have 48 hours (2 days with 1 population) + 432
(3 days with 6 population) = 480 hours assuming we are only looking at days 10/7 – 10/11. The biggest
thing is that I want it to be adjustable with the date slicer so that if I want to look at a different set of
dates I can.


I think a solution I saw was utilizing a cross join and then performing a measure from there, but let’s
assume that the dataset is way too big to consider a cross join. Is there another possibility?


Thanks in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi duisinvalid ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create measures.

    Measure = 
var _all_day=COUNTROWS(ALLSELECTED('Date'[Date]))
var _day_a=_all_day-COUNTROWS(FILTER(ALL('Table b'),[Day_Unopen]<=MAX('Date'[Date]) && [Day_Unopen]>=MIN('Date'[Date]) && [Factory]="A"))
var _day_b=_all_day-COUNTROWS(FILTER(ALL('Table b'),[Day_Unopen]<=MAX('Date'[Date]) && [Day_Unopen]>=MIN('Date'[Date]) && [Factory]="B"))
var _pop_a=CALCULATE(SUM('Table a'[Population]),FILTER(ALL('Table a'),[Machine_Type]=MAX('Table a'[Machine_Type]) && [Factory]="A"))
var _pop_b=CALCULATE(SUM('Table a'[Population]),FILTER(ALL('Table a'),[Machine_Type]=MAX('Table a'[Machine_Type]) && [Factory]="B"))
RETURN _day_a*_pop_a*24 + _day_b*_pop_b*24
    Measure 2 = SUMX(VALUES('Table a'[Machine_Type]),[Measure])

    (3) Then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi duisinvalid ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create measures.

    Measure = 
var _all_day=COUNTROWS(ALLSELECTED('Date'[Date]))
var _day_a=_all_day-COUNTROWS(FILTER(ALL('Table b'),[Day_Unopen]<=MAX('Date'[Date]) && [Day_Unopen]>=MIN('Date'[Date]) && [Factory]="A"))
var _day_b=_all_day-COUNTROWS(FILTER(ALL('Table b'),[Day_Unopen]<=MAX('Date'[Date]) && [Day_Unopen]>=MIN('Date'[Date]) && [Factory]="B"))
var _pop_a=CALCULATE(SUM('Table a'[Population]),FILTER(ALL('Table a'),[Machine_Type]=MAX('Table a'[Machine_Type]) && [Factory]="A"))
var _pop_b=CALCULATE(SUM('Table a'[Population]),FILTER(ALL('Table a'),[Machine_Type]=MAX('Table a'[Machine_Type]) && [Factory]="B"))
RETURN _day_a*_pop_a*24 + _day_b*_pop_b*24
    Measure 2 = SUMX(VALUES('Table a'[Machine_Type]),[Measure])

    (3) Then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi duisinvalid - you can create a measure in Power BI that calculates the total running hours for each machine type

    similar solved threads FYR:

    Solved: Calculate total hours worked from multiple check i... - Microsoft Fabric Community

    Time Calculation Summing Work Hours - Power BI - Enterprise DNA Forum

     

    Modify the table name as per your model:

     

    Total Running Hours =
    VAR TotalDaysOpen =
    COUNTROWS(
    FILTER(
    ALL('Factory'),
    'Factory'[Day_Unopen] >= MIN('Date'[Date]) &&
    'Factory'[Day_Unopen] <= MAX('Date'[Date])
    )
    )
    VAR HoursPerDay = 24
    VAR PopulationFactor = SUMX(
    'MachineTable',
    'MachineTable'[Population] * HoursPerDay
    )
    RETURN
    TotalDaysOpen * PopulationFactor

     

    To make sure the formula works dynamically with a date slicer, you will need to ensure that the date table is correctly linked to the factories and machines.

    Hope the above logic helps.

    • duisinvalid's avatar
      duisinvalid
      Regular Visitor
      1 year ago

      thanks for the reply! I gave it a try but this solution didn't sectionalize the factories and multiply it by the weight of the population of a given machine in a specific factory.

       

      I will keep working on it, but if you think that there's a way to get it's total hours that would be awesome.