Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculate Measures in two tables with different date columns (per month view required)

Hi all,

 

I really need your expertise with PowerBI Measures.

 

I have two source tables Incidents [ID, Incident Date, ...]  and Changes [ID, Change Date, ...] from which I want to calculate an incident per change rate on a monthly basis.

 

Therefore, I created two Measures to calculate both values as per different filters. So far so good:

 

Team A Change Count = CALCULATE(COUNT(Changes[ID]),'ChangeCreatorTeam'[Company]="Team A")
Team A Incident Count = CALCULATE(COUNT(Incidents[ID]),'ChangeRel'[Change Implementor]="Team A")
 
Now, I want to calculate     Rate X = Team A Incident Count / Team A Change Count    per month.
 
However, when I further try to calculate / visualise per month, it doesn't work as the two values refer to different "date" columns in their respective table.
 
Any idea how I can calculate the final rate per month? I hope the problem is clear to you. A million thanks for your support!
1 ACCEPTED SOLUTION

Hi @Anonymous,

 

I think you are going to want to turn your model into a star schema with at least a date table and a team table

 

richbenmintz_1-1600280772187.png

 

 

then you can create super simple measures like

 

Changes = COUNTROWS('Change Table')

Incidents = COUNTROWS('Incident  Table')

Incident Per Change = DIVIDE([Incidents], [Changes])

 

which will provide the following results

 

richbenmintz_0-1600280752141.png

The link below provides a pbix file with the tables and measures suggested.

 

sample.pbix 

 

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

7 REPLIES 7
Greg_Deckler
Community Champion
Community Champion

@Anonymous This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler Thank you for your prompt reply! But I am sorry, I think I did not get it yet...

 

You're saying I need to calculate the Min or Max values (shouldn't matter as the counts are static for the month, right?) out of my Measures? And then use these values to calculate the required quotient?

@Anonymous Need sample data as text in a table and expected output from the sample data to be more specific.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler Sure, please see sample data below.

 

Incident Table

Incident IDIncident DateTeamCategory
1000223.05.2020Team AStorage
1000330.05.2020Team BNetwork
1000430.05.2020Team ACompute
1000501.06.2020Team BStorage
1000623.06.2020Team CNetwork
1000704.07.2020Team AStorage
1000805.07.2020Team BStorage
1000910.07.2020Team ANetwork
1001007.07.2020Team BCompute

 

Change Table

Change IDChange DateTeamChange Type
20123.05.2020Team AEmergency
21023.05.2020Team BStandard
20230.05.2020Team BStandard
20330.05.2020Team AStandard
21130.05.2020Team AStandard
21230.05.2020Team BStandard
20401.06.2020Team AStandard
21301.06.2020Team AStandard
20523.06.2020Team BStandard
20604.07.2020Team AStandard
20705.07.2020Team BStandard
20907.07.2020Team AStandard
20810.07.2020Team CEmergency

 

I want to calculate / visualize the quotient (Incidents / Changes) per Team and month.

 

Example:

I am calculating the following counts using the measures above:

 

Team A:

- 2 Incidents, 3 Changes in May

- 2 Incidents, 2 Changes in July

 

Out of these numbers, I want to calculate the quotient Incidents per Changes (in this case 2/3=66,66% in May and 2/2=100% in July) and display it in a clustered bar chart with multiple teams per month.

 

Of course, I still want to be able to apply different filters (e.g. Change Type). Therefore, it must be somehow dynamically.

 

Thanks a million for your support!

Hi  @Anonymous ,

 

First create a month column in both 2 tables;

Then create 2 slicer table as below:

2.png1.png

Then create a measure as below:

Rate X = 
var _ChangeCount = CALCULATE(COUNT('Change Table'[Change ID]),FILTER(ALL('Change Table'),'Change Table'[Team]=SELECTEDVALUE('Slicer Table2'[Team])&&'Change Table'[Month]=SELECTEDVALUE('Slicer Table1'[Month])))
var _IncidentCount =CALCULATE(COUNT('Incident Table'[Incident ID]),FILTER(ALL('Incident Table'),'Incident Table'[Team]=SELECTEDVALUE('Slicer Table2'[Team])&&'Incident Table'[Month]=SELECTEDVALUE('Slicer Table1'[Month])))
Return
DIVIDE(_IncidentCount,_ChangeCount)

And you will see:

3.png4.png

For the related .pbix file,pls see attached.

 

Best Regards,

Kelly

If I have solved your issue,pls mark the reply as answered to close it!

 

Hi @Anonymous,

 

I think you are going to want to turn your model into a star schema with at least a date table and a team table

 

richbenmintz_1-1600280772187.png

 

 

then you can create super simple measures like

 

Changes = COUNTROWS('Change Table')

Incidents = COUNTROWS('Incident  Table')

Incident Per Change = DIVIDE([Incidents], [Changes])

 

which will provide the following results

 

richbenmintz_0-1600280752141.png

The link below provides a pbix file with the tables and measures suggested.

 

sample.pbix 

 

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Anonymous
Not applicable

Thank you! It's often easier as expected. I used a separate "Date" table and linked both tables to the "Date" table. When using the separate dates instead of the original columns, I achieved the right aggregation. Thank you for your support!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors