Forum Discussion

visittokiran's avatar
visittokiran
Frequent Visitor
4 years ago
Solved

two dimensional relationship

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 z...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
BA_Pete
Super User
4 years ago

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