Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

create a table from another table plus zeros

Dear all,    I'm struggling with the following and was hoping for some help. I have created a measure called "Rate" that looks like this if I use a matrix to visualize it:   Date Operator A ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi! What I ended up doing is a creatig a calculated column in the Event Data table that looks like this:

     

    Event_Month = TOTALMTD(COUNT('Event Data'[Event]),'Event Data'[Event Date Time].[Date])

    This counts how many events per month happened.

     

    Then I created a calculated column in the Utilization Data table to calculate the rate, which is the total event per month divided by the total hours for that month, for each operator and each car "model". I had to use an IF statement so that every time there were no events for a particular month, the result would be zero instead of a blank value. That allows me to calculate and visualize the average rate with a gauge visual and a time slicer for how many months I want.

     

    Event Rate = IF(DIVIDE(CALCULATE(MAX('Event Data'[Event_Month]),FILTER('Event Data',IF('Event Data'[OPERATOR]= 'Utilization Data'[Operator],1,0)), FILTER('Event Data', IF('Event Data'[Model] = 'Utilization Data'[Model], 1, 0)),FILTER('Event Data', IF('Event Data'[Year-Mon]='Utilization Data'[Year-Mon2], 1, 0))),'Utilization Data'[Total Monthly Hours],0)>0, DIVIDE(CALCULATE(MAX('Event Data'[Event_Month]),FILTER('Event Data',IF('Event Data'[OPERATOR]= 'Utilization Data'[Operator],1,0)), FILTER('Event Data', IF('Event Data'[Model] = 'Utilization Data'[Model], 1, 0)),FILTER('Event Data', IF('Event Data'[Year-Mon]='Utilization Data'[Year-Mon2], 1, 0))),'Utilization Data'[Total Monthly Hours],0),0)

     

    Thank you all for the help.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

v-jiascu-msft

 

 Hi! I've tried to create the three columns to count the type of events for each operator, each month. However I am not able to create more than 1 column as PBI will give me an error.

 

A circular dependency was detected: Utilization Data[Electrical], Utilization Data[Column], Utilization Data[Eelctrical].

 

It looks like I cannot create more than one calculated column in the Utilization Data table. Hence I'll have to review the approach again, i.e. utilize measures instead of calculated columns.

 

Thanks!

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

You can add three columns this way:

 

Electrical Failure =
VAR fp =
    LOOKUPVALUE (
        'Event Data'[Failure Type],
        'Event Data'[Year-Mon], [Year-Mon2],
        'Event Data'[Operator], [Operator],
        'Event Data'[Model], [Model]
    )
RETURN
    IF ( fp = "Electrical", fp, BLANK () )
Mechanical Failure =
VAR fp =
    LOOKUPVALUE (
        'Event Data'[Failure Type],
        'Event Data'[Year-Mon], [Year-Mon2],
        'Event Data'[Operator], [Operator],
        'Event Data'[Model], [Model]
    )
RETURN
    IF ( fp = "Mechanical", fp, BLANK () )
Hydraulic Failure =
VAR fp =
    LOOKUPVALUE (
        'Event Data'[Failure Type],
        'Event Data'[Year-Mon], [Year-Mon2],
        'Event Data'[Operator], [Operator],
        'Event Data'[Model], [Model]
    )
RETURN
    IF ( fp = "Hydraulic", fp, BLANK () )

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale