topic Re: Many to Many Measures in DAX Commands and Tips

Many to Many Measures

Jyaul1122 — Tue, 30 Sep 2025 05:44:39 GMT

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

Re: Many to Many Measures

rohit1991 — Mon, 20 Oct 2025 07:21:51 GMT

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:

Re: Many to Many Measures

Jyaul1122 — Tue, 30 Sep 2025 06:33:01 GMT

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.

Re: Many to Many Measures

mdaatifraza5556 — Tue, 30 Sep 2025 06:39:45 GMT

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.

Re: Many to Many Measures

rohit1991 — Mon, 20 Oct 2025 07:22:43 GMT

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.

Re: Many to Many Measures

Jyaul1122 — Tue, 30 Sep 2025 07:04:53 GMT

mdaatifraza5556

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

Re: Many to Many Measures

Jyaul1122 — Wed, 01 Oct 2025 13:35:24 GMT

rohit1991

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

Re: Many to Many Measures

BernardBonto — Thu, 02 Oct 2025 06:32:43 GMT

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

Re: Many to Many Measures

v-veshwara-msft — Mon, 06 Oct 2025 09:21:26 GMT

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.