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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
helen_p
Frequent Visitor

2 columns from seperate tables being filtered in DAX expression

Dear Power BI community

 

What do I want to achieve?

I am looking for some assistance to help create a DAX expression so I can filter across 2 different tables

1. 'Dim date table'[DaysFromToday]

2. 'Dim time table'[HourOfDay]

 

So I can show what?

I want to get the average number of discharges at the same point in time (Current hour) over the last 4 weeks

For example.  On average there were 15 discharges on a Friday for the last 4 weeks between 12pm-1pm

 

How is my data structured

My data is structured so that I can get a count of discharges for every day and fo every hour of day

 

SumOfDischargeCountDaysFromTodayHourOfDay
6-280
6-283
5-285
6-286
8-287
5-288
6-289
18-2810
14-2811
13-2812
17-2813
20-2814
24-2815
27-2816
31-2817
31-2818
20-2819
26-2820
11-2821
10-2822
7-2823

 

So far my DAX looks like this:

 

Test CROSSJOIN =
              CALCULATE (
            [SumOfDischargeCount],
            FILTER (
                CROSSJOIN ( ALL ( 'Dim date table'[DaysFromToday] ), ALL ( 'Dim time table'[HourOfDay] ) ),
            'Dim date table'[DaysFromToday]  = {-7} || 'Dim date table'[DaysFromToday] = {-14} || 'Dim date table'[DaysFromToday] = {-21} || 'Dim date table'[DaysFromToday] = {-28} || 'Dim time table'[HourOfDay] <= [Current hour]
            )
        )
 
What do I also need to consider?
 
I need to also consider making sure I get the average
 
 
Can anyone help assist me please
 
 
1 REPLY 1
helen_p
Frequent Visitor

Think I have solved my own issue

 

Test 2 =
CALCULATE ([SumOfDischargeCount]
,
'Dim date table'[DaysFromToday] = {-7} || 'Dim date table'[DaysFromToday] = {-14} || 'Dim date table'[DaysFromToday] = {-21} || 'Dim date table'[DaysFromToday] = {-28}, FILTER('Dim time table', 'Dim time table'[HourOfDay] = [Current hour] ))/4

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors