Forum Discussion

Jyaul1122's avatar
Jyaul1122
Helper III
10 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
Helper III
10 months ago

rohit1991 

Thanks for your reply, but Project P3 is missing. I would like to have all Project from Address table where the status is blank or not.

 

rohit1991's avatar
rohit1991
Super User
10 months ago

Hi Jyaul1122 

Could you please try below Steps:


1. Below is the sample data that I used to solve this problem

 

 

 

 

 

 

 

 

2. Create new Table

ProjectDim = 
DISTINCT (
    UNION (
        SELECTCOLUMNS ( 'Address', "Project", 'Address'[Project] ),
        SELECTCOLUMNS ( 'Status', "Project", 'Status'[Project] )
    )
)

3. Change Modeling

  • ProjectDim[Project] >> Address[Project] (One-to-Many)

  • ProjectDim[Project] >> Status[Project] (One-to-Many)

4. Create Meaure: 

Status by Month = 
VAR SelMonth = SELECTEDVALUE ( 'Status'[Month] )
RETURN
CALCULATE (
    MAX ( 'Status'[Status] ),
    TREATAS ( { SelMonth }, 'Status'[Month] ),
    TREATAS ( VALUES ( 'Address'[Project] ), 'Status'[Project] )
)

5. Right-click the Project field and enable the option Show items with no data.

 

 

 

  • Jyaul1122's avatar
    Jyaul1122
    Helper III
    10 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
      Community 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.