Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Inteq
New Member

Considering sales date as a criteria - relation many to many

 

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.

 

Inteq_4-1671138689340.png

Inteq_6-1671138959815.png

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.

Inteq_8-1671139304120.png

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

any tips are welcomed.

 

Thank you in advance.
Inteq

1 ACCEPTED SOLUTION
sturlaws
Resident Rockstar
Resident Rockstar

I would highly recommend to rewrite your model to this:

sturlaws_0-1671183934671.png

 

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_1-1671184531239.png

 

 

View solution in original post

3 REPLIES 3
sturlaws
Resident Rockstar
Resident Rockstar

I would highly recommend to rewrite your model to this:

sturlaws_0-1671183934671.png

 

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_1-1671184531239.png

 

 

sturlaws
Resident Rockstar
Resident Rockstar

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.

@sturlaws Thank you for the answer. My model below

Inteq_0-1671173148055.png

 

 

Output table should look like 

Inteq_2-1671169439102.png

Kind Regards,
Paweł

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.