Forum Discussion

gareta's avatar
gareta
Frequent Visitor
9 years ago
Solved

How to model data to generate dynamic rates with selectable dimensions

I'm looking for ideas for how to model and measure event rates across an inventory which also changes daily. I've solved this in prototype but suspect there are more refined models that might make th...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi gareta，

    Firstly, only when you have an entry for Date 1/2 in EventTable, you are able to get the following desired result.

     

    Create the following measures in your EventTable, please note that there is no relationship between two tables.

    Workers = COUNTROWS(FILTER(WorkerTable,WorkerTable[Date]=MAX(EventTable[Date])))
    Occurrences2 = CALCULATE(COUNTA(EventTable[Event]),FILTER(EventTable,EventTable[Event]<>""))
    Rate 2 = IF(ISBLANK([Occurrences2]/[Workers]),0,[Occurrences2]/[Workers])

    Then create a table visual using fields and mesaures of EventTable as shown in the following screenshot.

     

    Secondly, if you don’t have record for  Date 1/2 in EventTable,  create the measures below, and you will get the following table visual.

    Occurrences = COUNTA(EventTable[Event])
    Workers = COUNTROWS(FILTER(WorkerTable,WorkerTable[Date]=MAX(EventTable[Date])))
    Rate = [Occurrences]/[Workers]


    Thirdly, could you please describe more details about how you get the numbers of workers for 1/2, 1/3 in your last screenshot? Based on my test, when we group events of any type of above table visual, we just need to exclude Event field from the table visual. But this way, I get different numbers of workers for 1/2, 1/3.


    Thanks,
    Lydia Zhang

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi gareta，

Firstly, only when you have an entry for Date 1/2 in EventTable, you are able to get the following desired result.

 

Create the following measures in your EventTable, please note that there is no relationship between two tables.

Workers = COUNTROWS(FILTER(WorkerTable,WorkerTable[Date]=MAX(EventTable[Date])))
Occurrences2 = CALCULATE(COUNTA(EventTable[Event]),FILTER(EventTable,EventTable[Event]<>""))
Rate 2 = IF(ISBLANK([Occurrences2]/[Workers]),0,[Occurrences2]/[Workers])

Then create a table visual using fields and mesaures of EventTable as shown in the following screenshot.

 

Secondly, if you don’t have record for  Date 1/2 in EventTable,  create the measures below, and you will get the following table visual.

Occurrences = COUNTA(EventTable[Event])
Workers = COUNTROWS(FILTER(WorkerTable,WorkerTable[Date]=MAX(EventTable[Date])))
Rate = [Occurrences]/[Workers]


Thirdly, could you please describe more details about how you get the numbers of workers for 1/2, 1/3 in your last screenshot? Based on my test, when we group events of any type of above table visual, we just need to exclude Event field from the table visual. But this way, I get different numbers of workers for 1/2, 1/3.


Thanks,
Lydia Zhang

gareta's avatar
gareta
Frequent Visitor
9 years ago

This all looks good and I will work on replicating it as you describe. Thank you!

 

(Regarding worker count on 1/2 and 1/3, your numbers are accurate, my sample results are incorrect.)

  • gareta's avatar
    gareta
    Frequent Visitor
    9 years ago

    This is all very helpful, thank you Lydia. Anonymous

     

    One more question, I'm still unclear how to do this last bit. When I want to view issues that affected a subset of all Workers (eg, group by a certain selection of category):

     

    Date

    Event

    Occurrences

    Workers WHERE SubCatagory = X

    Rate

    1/1

    Injury

    1

    1

    1/1

    1/2

     

     

    		10

    1/3

    Dissolution

    1

    1

    1/1

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi gareta,

      Firstly, create a new table using DAX below. And create relationship between NewTable and EventTable using WorkerName field.
      NewTable = CALCULATETABLE(WorkerTable,WorkerTable[SubCatagory]="X")

      Then create the following measures.
      Measure = CALCULATE(COUNTA(NewTable[Date]),FILTER(NewTable,NewTable[Date]=Max(EventTable[Date])))
      Occurrences = COUNTA(EventTable[Event])
      Rate = [Occurrences]/[Measure]



      Thanks,
      Lydia Zhang