Calendar table to filter 2 tables that have existing Relationship aka Lapse New Trading

[malcolmwunOW]

I have 3 tables that has ambiguous relations - where the date table filters 2 table. 

At the same time the Status Table needs to filter Fact Sales.

My attemtp to overcome this, I wrote the following DAX but did not work

Total Sales = Calculate(sum(SALES),

                    TREATEAS(SUMMARIZE(Fact Sales [CUSTOMER]),Status Table [CUSTOMER]))

The desired outcome is in Image below. thank you in advanced

[v-menakakota]

Hi @malcolmwunOW ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-menakakota]

Hi @Eisha ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi @malcolmwunOW ,

I hope the provided information by mdaatifraza5556  is  helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you

[mdaatifraza5556]

Hi @malcolmwunOW 

You can use my first approach which is using LOOKUPVALUE DAX.
OR
correct your DAXusing below DAX.

saless =

    CALCULATE(

        SUM('Fact Sales'[Sales]),

        TREATAS(VALUES('Fact Sales'[Cust]), 'Status Table'[Cust]))

[mdaatifraza5556]

Hi @malcolmwunOW 
Remove the relationship between fact and status and in the fact table bring the status column using LOOKUPVALUE.
checkout the below DAX

status = LOOKUPVALUE('Status Table'[Status], 'Status Table'[MTH], 'Fact Sales'[MTH], 'Status Table'[Cust], 'Fact Sales'[Cust])

Drag the column from the calendar and the fact.

[Dangar332 · Accepted solution]

Hi, @malcolmwunOW 

Try below measure

Total Sales = 
Calculate(
  sum('fact sales'[SALES]),
  TREATEAS(all(Status Table [CUSTOMER]),Fact Sales [CUSTOMER]))

If still does not work then please provide Screenshot of your data model

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.