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
Applicable88
Impactful Individual
Impactful Individual

grouping/summarize over a related table

Hello,

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

The first one is the facttable:

OrdernumberMaterialsAmountNeededOrderDate
1001D5552002.07.2021
1002D7873903.07.2021
1003Z9995004.07.2021
100478761005.07.2021
1005H6M1506.07.2021
1006Z7338807.07.2021
1007Z9993908.07.2021
100878765009.07.2021
1009H6M1010.07.2021
1010Z7331511.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:

 

MaterialsOnStockReservedate
D555100002.01.2021
D787400002.01.2021
Z999200001.01.2021
78761234501.01.2021
H6M321501.01.2021
Z733764501.01.2021
Z999200005.01.2021
78761234505.01.2021
H6M321505.01.2021
Z733764505.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. 

 

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

 

MFelix_0-1625611111665.png

 

 

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

MFelix_1-1625611277841.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

8 REPLIES 8
MFelix
Super User
Super User

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

 

MFelix_0-1625584949066.png

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Applicable88
Impactful Individual
Impactful Individual

@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.

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:

 

MFelix_0-1625608232715.png

 

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)

MFelix_1-1625608448115.png

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Applicable88
Impactful Individual
Impactful Individual

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

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

 

MFelix_0-1625611111665.png

 

 

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

MFelix_1-1625611277841.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Applicable88
Impactful Individual
Impactful Individual

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:

Applicable88_1-1625636593091.png

 

Applicable88_0-1625636570614.png

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

Best. 

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:

 

MFelix_1-1625671800297.png

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.

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Applicable88
Impactful Individual
Impactful Individual

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

Helpful resources

Announcements
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors