Reply
Frequent Visitor
Posts: 5
Registered: ‎03-08-2017
Accepted Solution

How to model data to generate dynamic rates with selectable dimensions

[ Edited ]

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 this more sensible.

 

We have a WorkerTable updated daily:

Date

WorkerName

Catagory

SubCatagory

...

1/1

Worker1

A

X

 

1/1

Worker2

A

Y

 

1/2

Worker1

A

X

 

1/2

Worker2

A

Y

 

1/2

Worker3

B

X

 

1/3

Worker2

A

Y

 

1/3

Worker3

B

X

 

 

We have a daily EventTable:

Date

Event

WorkerName

1/1

Injury

Worker1

1/3

Confusion

Worker2

1/3

Dissolution

Worker3

 

We want to be able to see the rate of events against all workers:

 

Date

Event

Occurrences

Workers

Rate

1/1

Injury

1

2

1/2

1/2

 

 

3

0

1/3

Confusion

1

2

1/2

1/3

Dissolution

1

2

1/2

 

Or by a certain selection of category:

 

Date

Event

Occurrences

SubCatagory = X

Rate

1/1

Injury

1

1

1/1

1/2

 

 

1

0

1/3

Dissolution

1

1

1/1

 

And finally to be able to group events of any type:

 

Date

Events

Workers

Rate

1/1

1

2

1/2

1/2

0

2

0

1/3

2

3

2/3

 

 What is a good way to shape and model the data in powerbi to achieve this result?


Accepted Solutions
Moderator
Posts: 4,661
Registered: ‎03-10-2016

Re: How to model data to generate dynamic rates with selectable dimensions

[ Edited ]

Hi @gareta

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

 

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.
1.PNG

 

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]

2.PNG


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.
5.PNG4.PNG


Thanks,
Lydia Zhang

View solution in original post


All Replies
Moderator
Posts: 4,661
Registered: ‎03-10-2016

Re: How to model data to generate dynamic rates with selectable dimensions

[ Edited ]

Hi @gareta

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

 

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.
1.PNG

 

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]

2.PNG


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.
5.PNG4.PNG


Thanks,
Lydia Zhang

Frequent Visitor
Posts: 5
Registered: ‎03-08-2017

Re: How to model data to generate dynamic rates with selectable dimensions

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.)

Frequent Visitor
Posts: 5
Registered: ‎03-08-2017

Re: How to model data to generate dynamic rates with selectable dimensions

This is all very helpful, thank you Lydia. @v-yuezhe-msft

 

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

Moderator
Posts: 4,661
Registered: ‎03-10-2016

Re: How to model data to generate dynamic rates with selectable dimensions

[ Edited ]

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