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

[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 ?