Forum Discussion
Many to Many Measures
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.
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:
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.
Hi Jyaul1122
Could you please try below Steps:
1. Below is the sample data that I used to solve this problem2. 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.
-
