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 on month selection from slicer and display on  Address table(all column) in a report.

So that I wrote measure: Status_ = MAX('Status'[Status]) but its not working properly.

What I need, If month Jan 25 is selected then result:

if Feb 25 selected:

if March 25 selected:

means , I need to bring status from Status table by Project by month. How can I achieve using measures ?

Sample data:

Status Table  
Project(Status)MonthStatus
P125-JanHigh
P225-JanHigh
P125-FebLow
P225-FebHigh
P325-MarMedium
P425-MarVery Low

 

Adress Table  
Project(Address)Sub ProjectAddress
P1P1.1Japan
P1P1.2USA
P1P1.3England
P2P2.1Germany
P3P3.1Italy
P2P2.2Norway
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.

     

8 Replies

  • 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. Modeling:

     

    3. Created Measure :

    Status by Month = 
VAR SelMonth =
    SELECTEDVALUE ( 'Status'[Month] )
RETURN
MAXX (
    FILTER (
        'Status',
        'Status'[Project] = MAX ( 'Address'[Project] ) &&
        'Status'[Month] = SelMonth
    ),
    'Status'[Status]
)

    4. Outcome:


     

    • 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.

         

         

         

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    10 months ago

    Hi Jyaul1122 

    Could you please try the below dax to create the dax ?

    Selected Status =
    VAR SelectedMonth = SELECTEDVALUE('Status'[Month])
    VAR ThisProject = SELECTEDVALUE('Address'[Project(Address)])

    RETURN
    CALCULATE(
        MAX('Status'[Status]),
        FILTER(
            'Status',
            'Status'[Project(Status)] = ThisProject &&
            'Status'[Month] = SelectedMonth
        )
    )

     

     

     

    Also attached the pbix file for you reference.

    If this answers your questions, kindly accept it as a solution and give kudos.
  • BernardBonto's avatar
    BernardBonto
    New Member
    10 months ago

    Hi Jyaul1122,

     

    Maybe this can help. I have created a date table from min and max date value and use it as a filter.

     

    Table:
            Date = GENERATESERIES(MIN('Status'[Month]), MAX('Status'[Month]))
    Filter: 
            MonthYear = FORMAT('Date'[Date], "MMM-YYYY")
    Measure: 
           Max Status = COALESCE(MAX('Status'[Status]),"")
     

     

     

    Jan-2025

     

    Feb-2025

    Mar-2025

     

    Bernard