two dimensional relationship

[visittokiran]

Dear Team,

I have three data tables as follow 

1 Calander 

date	fy year	month
1/1/2019
till
7/11/2021	2021-22

2) data table 1

date	incident ID	zone	Consumer affected
7/11/2021	123445	1011	55000
6/11/2021	123444	1012	66000

3)data table 2

month-year	zone	total consumers
11-2021	1011	85000
11-2021	1012	70000

i have relationship between calander table and data table 1 using date column. and using zone column between data table 1 & 2.

I want to calculate ratio of affected consumer to total consumers for each zone on that month. 

How to use means when i selected Month filter same month's total consumer should be in denominator. 

[v-stephen-msft · Accepted solution]

Hi @visittokiran ,

For your needs, it seems to be easier to use DAX.

You can create the following measure

total consumers from table2 =
CALCULATE (
    SUM ( 'data table 2'[total consumers] ),
    FILTER ( 'data table 2', [month-year] = MAX ( 'Calendar'[MonthYear] ) )
)

When you filter MonthYear, the results is as follows.

You want to calculate the ratio, just create the following

Ratio = DIVIDE(SUM('data table 1'[Consumer affected]),[total consumers from table2])

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete]

Hi @visittokiran ,

It sounds like you need set set your data model up into a star schema structure, rather than trying to relate your two fact (data) tables together.

1) First, create a proper date column within your dataTable2. Power Query is pretty smart and should automatically do this is you change the data type of your month-year column to Date type.

2) Remove the relationship between dataTable1 & 2 [zone].

3) Relate calendar[date] to dataTable2[month-year] (now a proper date).

4) Create a [zone] dimension table which just contains a unique list of all zones. You can do this in Power Query very quickly by creating a new query something like this:

= Table.Distinct(Table.SelectColumns(dataTable2, "zone"))

I've used dataTable2 as the source here as it appears to be an SCD table that will contain all possible values.

5) Relate dimensionZone[zone] to dataTable1[zone] and dataTable2[zone].

6) Create your generic measure, something like this:

_affectedRatio =
DIVIDE(
  SUM(dataTable1[Consumer affected]),
  SUM(dateTable2[total consumers]),
  0
)

I've used SUM(dt1[Consumer affected]) as I don't know what behaviour you expect if there's more than one incident in a particular zone in the same month, so just amend this as necessary.

7) Create your visuals with calendar[month], dimensionZone[zone], and your generic measure and Power BI should apply the correct context filtering to give you your desired output.

Pete