Viewing Figures by Month / Week / Day which are not related

[jkenne01]

Hi,

I have tried to work this out by have not been able too I am sure there must be a way to do this in DAX.

My issue is the following:

I need to display Reach figures in a report. Reach is defined as the number of the target audience who are reached by a particular TV Channel in a given period. We have Reach figures for each TV Channel / Target Group for each Month, Week and Day of a given year.

These figuers are non additive and there is no relationship between them. For example, you do not roll up each Day Reach figure to give you the Weekly Reach figure. The Weekly Reach figure is calculated differently and we receive this as a seperate value and it is stored in its own table. The same holds true for the Days.

What I need to be able to do is have a Matrix where the user can drill down from Month->Week->Day and see the Reach figures for that Month, Week or Day. The Month, Week and Day figures are are stored in different tables in the Power BI model.

An example would be as follows.

If I have a TV Channel called ACME TV and it has a Monthly Reach for March 2018 for ALL ADULTS of 95.2. Within March 2018 there are 4 Week Ending Dates (04/03,11/03,18/03,25,.03) and each of these 4 Week Ending dates has its own Weeky Reach figures of 18.3, 25.4, 28,1,19.4. 

So what I need is to Display a Matrix where the top level row hierarchcy would be Month and this would show 95.2 for March 2018. Then the user drills down into March 2018 and sees 4 Week Ending dates with the 4 Weekly figures above. The Monthly figure is pulled from the Monthly Reach table and the Weekly figures pulled from the Weekly Reach table.

So I am thinking in the Value of the Matrix I would have a single Measure called Reach. Then there would be DAX code behind this measure that would decide what table (Monthly, Weekly or Daily) to pull the figures from depending on the level of the hierarchy the user is at?

Or maybe I need to remodel the data to have all the figures in a single table.

Would greatly appreciate any help!

Cheers.

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table and build a relationship from the Date column of the 3 Tables (Day, Week and Month) to the Date column of the Calendar Table.  In the Calendar Table create 2 columns for Week and Month.  To your visual, drag the Date, Month and Week from the Calendar Table.  Write this measure

Measure = IF(HASONEVALUE(Calendar[Date]),SUM(Day[Reach]),IF(HASONEVALUE(Calendar[Week]),SUM(week[Reach]),SUM(Month[Reach])))

Hope this helps.

[jkenne01]

Ashish you are a legend! To reply within 10 minutes with a working solution is incredible. This worked perfectly. Thanks for your help! Made my day!

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.