grouping/summarize over a related table

[Applicable88]

Hello,

I have two tables and they are connected via data model wit the column "Materials".

The first one is the facttable:

Ordernumber	Materials	AmountNeeded	OrderDate
1001	D555	20	02.07.2021
1002	D787	39	03.07.2021
1003	Z999	50	04.07.2021
1004	7876	10	05.07.2021
1005	H6M	15	06.07.2021
1006	Z733	88	07.07.2021
1007	Z999	39	08.07.2021
1008	7876	50	09.07.2021
1009	H6M	10	10.07.2021
1010	Z733	15	11.07.2021

The second table is a dimension table, but the materials can exisit more than in one row, since the date of reserve can be different, but the "OnStock" amount is already displaying the Total Amount availabe, so they shouldn't be summed up:

Materials	OnStock	Reservedate
D555	1000	02.01.2021
D787	4000	02.01.2021
Z999	2000	01.01.2021
7876	12345	01.01.2021
H6M	3215	01.01.2021
Z733	7645	01.01.2021
Z999	2000	05.01.2021
7876	12345	05.01.2021
H6M	3215	05.01.2021
Z733	7645	05.01.2021

I need a table variable inside a measure. 

How can I summarize/grouping these two tables over a related relationship without adding up the amounts "OnStock" of the materials, since they always displaying the total amount available already?

The summarize/grouping should return the counts of materials and the count of orders with these materials involved. 

This I hope that I can drag into a card visual and also a table visual. 

Thank you very much in advance.

Best. 

[MFelix]

Hi @Applicable88 ,

Not sure if I understood your requirement, you want to have a calculation of the number of materials and lines so for example if we pick up the 7876 the result is 2 order and 1 material.

In this case you only need to make a dimension table with the distinct values of the products and make a relationaship between this and the other 2 tables then use the new table on your visualizations and make the count of the lines on the other tables

For the calculation you just need to had the columns from each tabel and summarize on sum or make a measure similar to this one:

Count order = COUNT(facttable[Materials])

PBIX file attach.

[Applicable88]

@MFelix , thank you very much for the effort. I understand what you did. But is there a way to instead put it in a calculated table, where I can somehow get the materials distinct, or like with the values()-function. I just don't understand how to wrap that up to get practically the table that you did via the data model. Otherwise in a real usecase I would have to build and connect plenty of tables via data model. 

Hope you have an solution to that. 

Thanks.

Best.

[MFelix]

Hi @Applicable88 ,

the only table I created is the one with the distinct material that acts like a dimension table, the rest is create using the relationship that I have created between that table and the other two.

This table can be create in the editor or using dax by making a formula similar to this one:

Mat = DISTINCT( UNION(DISTINCT(facttable[Materials]), DISTINCT(Materials[Materials])))

Now you only need to setup the relationship like this:

The rest is just visualization working so I make the table with the following columns:

• Materials (from the new table)
• Materials from the onstock (summarized to count)
• Materials from the facttable (summarized to count)

Instead of Materials you can use what ever column you want from the onsotck and facttable.

Hope this helps and if still not clear please reach out. I will try and make it clearer or arrange another solution if this is not the best one.

[Applicable88]

@MFelix , I try to have it in a calculated table function. Why? because I can use it as a variable in another measure if needed, where I can make further calculation. 

I did something like this:

Summarizetable = summarize(facttable, facttable[Ordernumbers],facttable[OrderDate],related(Onstock[Materials]) "Ordercount", calculate(count(facttable[Ordernumber]),"MaterialSum", related(sum(Onstock)))

What I did try to achieve is to calculate this table instead of making a physical table for allocation.

As you can see, the problem lies there , when I want to make a kind of group by, but the column is on another table. So how to summarize the column with another one which is related through data model? 

[MFelix · Accepted solution]

Hi @Applicable88 ,

You can only use the RELATED function when you have a relationship between both table in this case between the on stock and the facttable, that is what I did creating the dimension table.

Check information in this link.

In Power BI you don't need to create tables to use on measures, this syntax that you wrote can be written within a measure and make the calculations based on this using the syntax below:

Measure name =
var variablename = SUMMARIZE(...)

Return
SUMX(variablename, [column of varialbename table])

however this implies that you use an aggregation measure like SUMX that allows to return the final value.

A best practice in this case is not to create a table with this calculation but a measure since gives you more flexibility and does no make your model grow. 

Another point if this best practice is that you should have a relationship.

Not having the relationship you should rewrite your table to something similar to this:

Summarizetable =
SUMMARIZE (
    facttable,
    facttable[Materials],
    "MMM", COUNT ( facttable[Materials] ),
    "CountMaterials",
        CALCULATE (
            COUNT ( Onstock[Materials] ),
            FILTER ( ALL ( Onstock[Materials] ), Onstock[Materials] = facttable[Materials] )
        ),
    "SumStock",
        CALCULATE (
            SUM ( Onstock[OnStock] ),
            FILTER ( ALL ( Onstock[Materials] ), Onstock[Materials] = facttable[Materials] )
        )
)

Has refered to use this syntax you don't need to have the table on your model this can be used in a measure check result below:

Onstock measure = 
var SummarizetableVariable =
SUMMARIZE (
    facttable,
    facttable[Materials],
    "MMM", COUNT ( facttable[Materials] ),
    "CountMaterials",
        CALCULATE (
            COUNT ( Onstock[Materials] ),
            FILTER ( ALL ( Onstock[Materials] ), Onstock[Materials] = facttable[Materials] )
        ),
    "SumStock",
        CALCULATE (
            SUM ( Onstock[OnStock] ),
            FILTER ( ALL ( Onstock[Materials] ), Onstock[Materials] = facttable[Materials] )
        )
)
return
SUMX(SummarizetableVariable, [SumStock])

[Applicable88]

okay @MFelix , thank you for showing me how to do it when no relation exist. That approach reminds me of SQL where-condition. 

I just want to understand for sure why I coulnd't summarize two columns based on two different tables, but they DO have a relation, because I used your pbix file as an example where you prepared a relation already:

As you can see with related() I couldn't establish it, even the tables have a relationsship. What did I do wrong?

Best. 

[MFelix]

Hi @Applicable88 ,

The use of the related can only be done in a calculated formula where the row context is unambiguous since the link between the fact table and the On stock has a many to many this get ambigous.

In this case and having a relationship one to many you don't even need to have the related just pick up the values from the materials table  and the SUM of the On stock:

Has you can see the result is picking up the correct value for each line however the details is higher than the previous table since I added the order into the calculation.

[Applicable88]

@MFelix thank you very much for the thorough explanation:)