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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
REGHnoob
Frequent Visitor

Count values from measure that's calculated from two tables

Hi all

Honestly I feel a bit silly. But I just can't get my head around this supposedly simple problem.

 

I have a measure (M3) that calculates the ratio between two other measures (M1 and M2). M1 and M2 are based on values from separate tables.

 

The two separate tables are both related to  date, time and location dimensions. Table 1 is further related to a handfull of type dimensions. I need to be able to dynamically slice by both type, date, time and location. 

 

T1 is about 40 mill rows and T2 is about 5 mill rows. Size and not least datasources prevent me from creating a hybrid table so the soulution must be entirely measure based.

 

M1 averages the sum pr day and hour based on the quarter column.

M2 averages the sum pr day and hour based on the Value column

M3 divides M2 with M1 to return the ratio between the two

Easy.

 

How do I count the number of times the ratio (M3) exceeds a value  (i.e. 5) and display the count in a matrix with location, date, time and types from the different dimensions.

 

T1 on the left T2 on the right

Table for M1Table for M1Table for M2Table for M2

 

 Best regards

K

0 REPLIES 0

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.