Measures based on two tables correlated to filter

[Maieev]

Hi community,

I have one dim table and two fact tables. Dim table has country_code and country_name columns.

First fact table has country_code column and orders column.

Second fact table has tasks_names column (task to process orders for a country) where first letters are country codes and time spend on task column.

I would like to write a measure for speed so sum of orders for a country divided by time spent on task for a country.

How to do that so it would be dynamic? I now I can write a measure for every task and country but is there a way to write one measure because I would like to then put it on chart and be able to use slicer with countries and when picking a country to see speed for picked one :).

Sorry for no file I'm on the phone but maybe someone could give me some ideas as I need them badly haha. I will also come back with file if needed. Thanks, community is great!!!!

[Maieev · Accepted solution]

@Akshaan sorry but I don't see what you did in the file except from putting fields in table?

Anyway I figured this out with measure:

Speed Measure =

VAR TotalOrders =

    COUNT(Orders[Order Number])

VAR TotalTimeSpent =

    CALCULATE(

        SUM(TasksKPI[Hours]),

        RELATEDTABLE(DimTaskSpeed),

        FILTER(

            TasksKPI,

            CONTAINS(DimTaskSpeed, DimTaskSpeed[Country_code], TasksKPI[Country_code]) &&

            CONTAINS(DimTaskSpeed, DimTaskSpeed[Tasks], TasksKPI[Tasks])

        )

    )

RETURN

    DIVIDE(

        TotalOrders,

        TotalTimeSpent

    )

[Akshaan]

Hi @Maieev you can use this link to get the solution pbix https://drive.google.com/file/d/1ACi1f1VebsLdb_n-O3hZHefk1OFZH59e/view?usp=sharing

Let me know if you still need any help.

[Akshaan]

Hi @Maieev, on the basis of my understanding, there could be certain ways to deal with this problem:

1st Approach:
1. Add a calculated column to get the country codes in second fact table using task-name column.

2. Normalize the data model a bit so, you can merge these two fact table using the country code column.

2nd Approach:
Create a DAX measure using SUMMARIZE and ADDCOLUMNS.

Please share your .pbix file if it doesn't works for you.

[Maieev]

https://www.dropbox.com/scl/fo/5jp8qe5lx3ysj8tf80w78/h?dl=0&rlkey=ta7tx964m6n7vmnsto117pxlk

I dropped a pbi file and excel file that I connected with fact data.

I must admit that I'm not sure how to execute your ideas by myself :).