Considering sales date as a criteria - relation many to many

[Inteq]

Hello, 

I have 2 months of data. I try to filter sales by type of client. The problem is type of client type can be different each month as per below table (example) relation many to many.

When I try to visualize this data in the table I get incorrect values e.x. For Oct 2022, Type of Client = 1 I get value of 600 because Client 2 has Client Type = 1 in Nov 2022.

I would like to create a measure that consider 'type of client' for value month.  

any tips are welcomed.

Thank you in advance.
Inteq

[sturlaws · Accepted solution]

I would highly recommend to rewrite your model to this:

Your current model with many-to-many connections and bi-directional filtering will make it quite hard to construct even the simplest measures(which obviously is the reason you have posted your question).

To modify your model, open Transform Data/Power Query, and merge Sales with ClientType on ClientName and Date. This will add client type to your sales table. As the the ClientType-table does not contain any other interesting information, it is not strictly neccessary to keep it, but it does not harm either.

Now you can get your desired output like this:

[sturlaws]

Hi, @Inteq,

could you provide a table displaying the desired outcome? And could you also provide a screen shot of your data model?

Assuming you have no relationship between you tables, you can use this measure:

Measure1 =
VAR _clientType =
    CALCULATE ( SELECTEDVALUE ( ClientType[Type of client] ) )
VAR _clientsFromType =
    CALCULATETABLE (
        VALUES ( ClientType[ClientName] ),
        FILTER ( ClientType, ClientType[Type of client] = _clientType )
    )
VAR _month =
    CALCULATE ( SELECTEDVALUE ( ClientType[Date] ) )
RETURN
    SUMX (
        CALCULATETABLE (
            ClientValue,
            FILTER (
                ClientValue,
                CONTAINS ( _clientsFromType, ClientType[ClientName], ClientValue[ClientName] )
                    && ClientValue[Date] = _month
            )
        ),
        ClientValue[ValueCol]
    )

but this is rather complex for such a simple task, you would be able to write a much simpler statement if your model your data properly

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Inteq]

@sturlaws Thank you for the answer. My model below

Output table should look like 

Kind Regards,
Paweł