Forum Discussion

Jyaul1122's avatar
Jyaul1122
Icon for Helper III rankHelper III
11 months ago
Solved

Many to Many Measures

Hello, I have two tables Status and Address in relation with many to many via Project (status) and Project(address) column. I would like to get status of Project from Status table based o...
Need Help
  • v-veshwara-msft's avatar
    v-veshwara-msft
    10 months ago

    Hi Jyaul1122 ,

    Thanks for reaching out to Microsoft Fabric Community.

    I tested the scenario using the same sample data shared above.

    Since TREATAS was causing performance issues in your model, here's an alternative approach using FILTER and IN, which avoids TREATAS and performs better in heavier models.

    Here's the measure:

    Status by Month = 
VAR SelMonth = SELECTEDVALUE('Status'[Month])
RETURN
CALCULATE (
    MAX('Status'[Status]),
    FILTER (
        ALL('Status'),
        'Status'[Month] = SelMonth &&
        'Status'[Project] IN VALUES('Address'[Project])
    )
)

     

    Please find attached .pbix for reference and reach out for any further assistance.
    Thank you.

     

    Thanks rohit1991 , mdaatifraza5556 and BernardBonto for your valuable inputs.

     

Jyaul1122's avatar
Jyaul1122
Icon for Helper III rankHelper III
11 months ago

rohit1991 

Treatas function is taking long time to respond as my model is to heavy , Could you please try with another funtion ?

v-veshwara-msft's avatar
v-veshwara-msft
Icon for Community Support rankCommunity Support
10 months ago

Hi Jyaul1122 ,

Thanks for reaching out to Microsoft Fabric Community.

I tested the scenario using the same sample data shared above.

Since TREATAS was causing performance issues in your model, here's an alternative approach using FILTER and IN, which avoids TREATAS and performs better in heavier models.

Here's the measure:

Status by Month = 
VAR SelMonth = SELECTEDVALUE('Status'[Month])
RETURN
CALCULATE (
    MAX('Status'[Status]),
    FILTER (
        ALL('Status'),
        'Status'[Month] = SelMonth &&
        'Status'[Project] IN VALUES('Address'[Project])
    )
)

 

Please find attached .pbix for reference and reach out for any further assistance.
Thank you.

 

Thanks rohit1991 , mdaatifraza5556 and BernardBonto for your valuable inputs.