Many to Many Measures

[Jyaul1122]

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)	Month	Status
P1	25-Jan	High
P2	25-Jan	High
P1	25-Feb	Low
P2	25-Feb	High
P3	25-Mar	Medium
P4	25-Mar	Very Low

Adress Table
Project(Address)	Sub Project	Address
P1	P1.1	Japan
P1	P1.2	USA
P1	P1.3	England
P2	P2.1	Germany
P3	P3.1	Italy
P2	P2.2	Norway

[BernardBonto]

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

[mdaatifraza5556]

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.

[Jyaul1122]

@mdaatifraza5556 

Thanks for your idea but slicer is not going to work.

[rohit1991]

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]

@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]

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]

@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 · Accepted solution]

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.