Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I know the most beneficial relationship with tables is always 1:n, which is the standard facttable to dimensiontable relationship. I'm not sure I can change my data model, since in reality its much more complex, but still I would like to know how to deal with this relationship where I cannot use the related function:
I have one facttable with a reorcurring Material ID's:
Factable
Order ID | Material | Date | Finishdate | |||
1234 | AAA | 01.01.2021 | 02.01.2021 | |||
1235 | BBB | 02.01.2021 | 03.01.2021 | |||
1236 | CCC | 03.01.2021 | 04.01.2021 | |||
1237 | DDD | 04.01.2021 | 05.01.2021 | |||
1238 | EEE | 05.01.2021 | 06.01.2021 | |||
1238 | AAA | 06.01.2021 | 07.01.2021 | |||
1238 | BBB | 07.01.2021 | 08.01.2021 | |||
1238 | CCC | 08.01.2021 | 09.01.2021 | |||
1238 | DDD | 09.01.2021 | 10.01.2021 | |||
1238 | EEE | 10.01.2021 | 11.01.2021 |
The facttable is connected with the filtertable, which only consider materials AAA,BBB and CCC:
Filtertable
Filtertable | WorkplaceID | |
AAA | TRT | |
BBB | TRT | |
CCC | TRT |
And this Filtertable is again connected with a table which consist of many different manufacturing times for each materials. The times and processcode cannot be grouped into one row, since I sometimes need different Processcodes for different Material calculations, I need to leave them it their respective own rows:
ManufacturingTime
Material | ProcessCode | Time1 | Time2 | |||
AAA | 10 | 20 | ||||
AAA | 20 | 10 | 20 | |||
BBB | 10 | 100 | ||||
BBB | 20 | 50 | 100 | |||
CCC | 10 | 30 | 10 | |||
CCC | 20 | 10 |
My task is to write a measure where I sum up the manufacturing for the materials in the facctable. One job of the filtertable is to provide connections only for certain materials. (The main reason is that different processtimes are unfortunately in different Manufacturing tables, depending on materialtype.)
In one measure I want to sum the Time1 with the processcode 20 f.
And in another measure I need to sum Time 2 of both Processcodes.
Since I have a n:1:n relationship, I cannot use the related function.
Every sum or sumx code I wrote end up having each row have the same amount displayed or something like the following table.
Apparently the filtering cannot work through n:1:n relationships, so the total is not summed up:
Order ID | Material | Date | Finishdate | MyMeasure | ||||
1234 | AAA | 01.01.2021 | 02.01.2021 | 50 | ||||
1235 | BBB | 02.01.2021 | 03.01.2021 | 50 | ||||
1236 | CCC | 03.01.2021 | 04.01.2021 | 50 | ||||
1237 | DDD | 04.01.2021 | 05.01.2021 | 50 | ||||
1238 | EEE | 05.01.2021 | 06.01.2021 | 50 | ||||
1238 | AAA | 06.01.2021 | 07.01.2021 | 50 | ||||
1238 | BBB | 07.01.2021 | 08.01.2021 | 50 | ||||
1238 | CCC | 08.01.2021 | 09.01.2021 | 50 | ||||
1238 | DDD | 09.01.2021 | 10.01.2021 | 50 | ||||
1238 | EEE | 10.01.2021 | 11.01.2021 | 50 | ||||
Total | 50 |
Note I already turned on bidirectional filtering on all tables.
Help is very much appreciated.
Thank you very much in advance.
Best.
Solved! Go to Solution.
@Applicable88 and @TheoC I would caution AGAINST the cross filter direction set to both unless you're sure you need it.
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
You don't need it in this scenario, just need a bit of DAX magic to get the context correct. SUMX will do the trick nicely.
This might not be the most efficient measure for this, but it does the trick:
UPDATED: IMPORTANT Lesson- do NOT use SUM or aggregate functions in calculated columns (that includes virtual calculated columns such as in the SUMX) - that's what I get for replying late at night! I have updated the below measures so they are correct now, as they use the CALCULATE to allow use to aggregate within a column.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@Applicable88 Let me know if this video makes any sense or is helpful. Apologies for my sloppy solution last night and hope this clarifies things a bit! 🙂
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@Applicable88 Let me know if this video makes any sense or is helpful. Apologies for my sloppy solution last night and hope this clarifies things a bit! 🙂
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy wow, kudos to you. Thanks for the great support and the wonderful youtube video. Very good explained and seemingly you have very much experience in explaining things! 🙂
@Applicable88,@TheoC even though @AllisonKennedy gave you a solution, I have devised it in a different way and from the current structure, that does not inflate the model size (I would definitely do that in a situation, unless I absolutely don't need to).
In the current data model, FILTERTbl filters FactTbl and FILTERTbl filters ManufacturingTbl.
You are bringing your axis from FactTbl in the Viz for this purpose and performing aggregation on ManufacturingTbl.
With the current structure, Fact has no way to reach Manufacturing.
Without needing to activate bidirectional filtering which introduces a lot of ambiguity in the model, the minimum requirment for you to create an indirect relationship between Fact and Manufacturing (n:n) where Fact Filters Manufacturing.
Once you have that, you need to write following measurs to reach your goal
_time1Manufacturing =
VAR _currentlyViisibleMaterialFromFact =
MAX ( FactTbl[Material] )
VAR _sumManufacturing =
CALCULATE (
SUM ( ManufacturingTbl[Time1] ),
TREATAS (
{ ( { _currentlyViisibleMaterialFromFact }, 20 ) },
ManufacturingTbl[Material],
ManufacturingTbl[ProcessCode]
),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
RETURN
_sumManufacturing
_time2Manufacturing =
VAR _currentlyViisibleMaterialFromFact =
MAX ( FactTbl[Material] )
VAR _sumManufacturing =
CALCULATE (
SUM ( ManufacturingTbl[Time2] ),
TREATAS ( { _currentlyViisibleMaterialFromFact }, ManufacturingTbl[Material] ),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
RETURN
_sumManufacturing
Also, if you need this on the subtotal level too. There are two versions of subtotal and I am not sure which one you would need
_t1Subtotal1 =
SUMX (
ManufacturingTbl,
VAR _currentlyViisibleMaterialFromFact =
CALCULATE ( MAX ( FactTbl[Material] ) )
VAR _sumManufacturing =
CALCULATE (
SUM ( ManufacturingTbl[Time1] ),
TREATAS (
{ ( { _currentlyViisibleMaterialFromFact }, 20 ) },
ManufacturingTbl[Material],
ManufacturingTbl[ProcessCode]
),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
RETURN
_sumManufacturing
)
_t2Subtotal1 =
SUMX (
ManufacturingTbl,
VAR _currentlyViisibleMaterialFromFact =
CALCULATE ( MAX ( FactTbl[Material] ) )
VAR _sumManufacturing =
CALCULATE (
SUM ( ManufacturingTbl[Time2] ),
TREATAS ( { _currentlyViisibleMaterialFromFact }, ManufacturingTbl[Material] ),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
RETURN
_sumManufacturing
)
_t1Subtotal2 =
VAR _subTotalLevel =
SUMX (
ManufacturingTbl,
VAR _currentlyViisibleMaterialFromFact =
CALCULATE ( MAX ( FactTbl[Material] ) )
VAR _sumManufacturing1 =
CALCULATE (
SUM ( ManufacturingTbl[Time1] ),
TREATAS (
{ ( { _currentlyViisibleMaterialFromFact }, 20 ) },
ManufacturingTbl[Material],
ManufacturingTbl[ProcessCode]
),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
VAR _sumManufacturing2 =
CALCULATE (
SUM ( ManufacturingTbl[Time1] ),
FILTER (
ManufacturingTbl,
ManufacturingTbl[Material] = _currentlyViisibleMaterialFromFact
&& ManufacturingTbl[ProcessCode] = 20
),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
RETURN
IF ( HASONEVALUE ( FactTbl[Material] ), _sumManufacturing1, _sumManufacturing2 )
)
RETURN
_subTotalLevel
_t2Subtotal2 =
VAR _subTotalLevel =
SUMX (
ManufacturingTbl,
VAR _currentlyViisibleMaterialFromFact =
CALCULATE ( MAX ( FactTbl[Material] ) )
VAR _sumManufacturing1 =
CALCULATE (
SUM ( ManufacturingTbl[Time2] ),
TREATAS ( { _currentlyViisibleMaterialFromFact }, ManufacturingTbl[Material] ),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
VAR _sumManufacturing2 =
CALCULATE (
SUM ( ManufacturingTbl[Time2] ),
FILTER (
ManufacturingTbl,
ManufacturingTbl[Material] = _currentlyViisibleMaterialFromFact
),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
RETURN
IF ( HASONEVALUE ( FactTbl[Material] ), _sumManufacturing1, _sumManufacturing2 )
)
RETURN
_subTotalLevel
pbix is attached here
https://1drv.ms/u/s!AkrysYUHaNRvhcV23WX0LZIKtlCfpQ?e=xNzPcb
Wow, @smpa01 thanks for the effort as well. I never seen Userelationship in action. I see that a long code is necessary to over come 1. bad data model and 2. no bidrectional crossfiltering.
Thanks for showing me alternatives!
Best regards.
@Applicable88 and @TheoC I would caution AGAINST the cross filter direction set to both unless you're sure you need it.
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
You don't need it in this scenario, just need a bit of DAX magic to get the context correct. SUMX will do the trick nicely.
This might not be the most efficient measure for this, but it does the trick:
UPDATED: IMPORTANT Lesson- do NOT use SUM or aggregate functions in calculated columns (that includes virtual calculated columns such as in the SUMX) - that's what I get for replying late at night! I have updated the below measures so they are correct now, as they use the CALCULATE to allow use to aggregate within a column.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy I don't know when you wrote the update...but I'm thankful that you did. I also discovered, if there is only sumx(facttable), sumx.....without wrapped in calculate, the Grand Total value simply isnt't right.
I think its an context transition problem? At least I guess so. Since not every material in the facttable is actually represented in the filtered or Manufacturingtable, we need to filter the Materialcolumn in the Facttable through context transition.
Thank you so much.
All the best.
@AllisonKennedy Just out of curiousity, why did you go to the effort of creating a new table and then adding a bi-directional / cross-directional relationship if you are against it?
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@TheoC Great question!
I'm only against bi-directional relationship on many to one/one to many relationships (and it's not just me - it's a widely accepted rule of thumb).
With a 1 to 1 relationship, you don't have a choice - it must be bi-directional. That relationship essentially converts the 'FilterTable' and 'NewFilterTable' into one table. Just a bit lazy so didn't feel like merging the WorkplaceID into the NewFilterTable, but that again would make it slightly more performant on larger datasets so potentially would do that in Power Query and just get rid of the 'FilterTable' all together in favour of the 'NewFilterTable' that contains all materials and only the WorkplaceID, etc, values for the Materials that have them.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy again, I am just unsure how you are using best practice if you have created an unnecessary table and additional measures to achieve the same output?
In terms of my solution, it does work. It's not eventual, it's literal. For example, the screenshots are of a PBIX I put together to create a solution for @Applicable88. I'm unsure where you have somehow come to think it will "eventually" work when the screenshots are the output...
Anyway, thank you kindly for your input and your views on elements of best practice. Have a good day 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@TheoC Your numbers are completely different to my solution, I'm not trying to step on your toes, just had a different interpretation of the problem and while I was here, as I always do, tried to explain WHY I have done something. I wanted to take special care to explain why my solution was different to the one already provided, as I do not normally post on a thread that already has a reply. However, I had started on this earlier and got taken away, and since then you have provided a possible solution.
Your solution only returns the firstnonblank value, and as you have mentioned yourself in the post, it does not take Process Code into account. You are correct - this info is not in the FactTable. @Applicable88 specified how they needed this to be calculated.
Using the screenshot you so helpfully provided (top), compared with my results (bottom), I have highlighted the corresponding Order numbers to see the differences in our calculations:
I did forget to add Order ID to my visuals, so have done that now to help show the differences in our two proposed solutions. We'll just have to wait for @Applicable88 to let us know if either of us have been able to help.
Again, my measures will work fine in their model as is, I just can't in good conscious provide only half a solution, which means if I'm providing a measure to a data model that I know could cause issues further down I will say something.
As I have mentioned, it's not my opinion or a best practice thing for the bi-directional one to many relationships, it causes ambiguity in your model and you should stay away from it: https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
The Dim table was just a bonus, see latest version of attached which as merged the two dim tables into 1, getting rid of the 1:1 relationship (could still work with the existing filter table, but that makes life more difficult to report on the Materials not present in that table).
Here's a bigger screenshot of the differences between your screenshots and mine:
PS, loving the discussion!
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Wow @AllisonKennedy Thank you so much for your effort and thorough explanation. I knew with that data model it would be more controversial. As with many things in the "real world" very often there is no perfect data structure nor perfect data model a given thing. Even if there would be no FilteringTable, the ManufacturingTime table can never be grouped by, because of different usage of the Time1 and Time2 for different manufacturing processes of different materials. And that means I would end up anyway having a m:n relationship between facttable and manufacturingTime table.
I dowloaded the file just now. It's so interesting! I never seen three sumx in succession, here used to filter the ProcessCode 20. It never came to my mind that it can be build like that.
But I already encounter one problem that I dealt with before, but this time another way round:
@AllisonKennedy I see that in a non-material selection we don't have the right "Totals":
only after selection of ONE material I get the right totals:
After adding only one more material I get the wrong totals:
same goes for selecting all materials:
I watched a few tutorials about "fixing the totals", but I don't think they address the very same problem. Is there a way to solve this too?
Thank you very much so far. I learnt a lot from that.
@Applicable88 I've removed my recommendation as @AllisonKennedy has put together a solution that ticks all boxes.
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@AllisonKennedy makes complete sense! The solution you've provided definitely aligns to what @Applicable88 was after as I did not read the request carefully.
In terms of the bi-directional elements, couldn't agree more. It's definitely better practice. I also like the adjustment to remove the additional table. Well done!
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Certainly agree about the cross-directional relationships. The only reason I added the the cross-directional relationship was to mimic that presented by @Applicable88. The outputs of my solution remain the same with one direction relationships, without the need to create additional tables, etc.
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@TheoC My solution also works without needing to create additional tables - I just have some spare time today so am going the extra mile to give my two cents (for what it's worth) about best practice - the Dim table should ideally include ALL the values for materials, not just some of them.
Your solution will work eventually, just requires columns rather than measures, so a bit less dynamic, but not sure that's an issue in this case. Your solution might also be a bit slower on larger dataset simply because it's doing a lookup rather than relying on the built in relationships (which your solution doesn't require at all).
What I don't think your solution does (yet) is account for the Process - you need to add that to the filter function to account for just Process 20, and then also need to sum Process 20 and 30 for the other required measure. 😀
I see you've added another reply just now so will go read that...
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi @AllisonKennedy, while creating a more realistic dataset for @TheoC according to my case, I just tried out your measure with the two sumx function. As you said it should work also without creating a new table.
https://drive.google.com/file/d/1MM8V_eBiD3GAAM3yswQQUKmle0WpAhQ6/view?usp=sharing
Interestingly now the totals are also right! Before and after filtering.
For instance that is again the ManufacturingTime table:
In this example I want to have 4 different Measures:
1. Show sum of all Preptime of a Material
2. Show sum of all ManuTime of a Material
3. Show sum of Preptime, but only of ProcessCode40
4. Show sum of Manutime, but only of ProcessCode30
And of course everytime the Totals should show right summed value.
Facttable with no Materialnumber selected:
The first PrepTime and ManuTime are the basic sum options of a integer field.
PreptimeSum and ManutimeSum are Measures á la sum(Preptime) and sum(ManuTime).
As expected, because of the N:1:N relationship the first four aggregations columns fail to show the total correctly, because the datamodel cannot utilize Related()-function or whatsoever. And the times are located "behind" the other side of the 1:N relationship.
The last two measure are the interesting ones:
More Materialnumber selected:
The totals are absolutely right now, before and after filtering the materials.
@AllisonKennedy , can you please tell me whats actually happening in the measure where sumx is wrapped up in another sumx, that it can find the right filter context, wheras the other measures only managed to sum the Times for each Materialnumber once?
And why this for example won't work:
I've actually made a video - too hard to explain in writing!!!
Waiting for it to upload to Youtube, but will share the link shortly.
I made one FATAL mistake that I will now fix in my post - do NOT use SUMX or SUM inside a calculated column (this also means don't nest SUMX functions unless you put a CALCULATE around it).
Here is the correct formula IF you have a PROPER DIM_Material table!!!!!!! (which you do not right now - I explain this in the video).
In the attached file (below signature) I have updated to use a proper DimMaterial table instead of the filter table, but please let us know how you get on!
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@Applicable88, thank you for putting this together. I will take a look early next week when in front of the computer again. In saying this, @smpa01 has put together some amazing pieces of dax as well as amended the existing data model relationships slightly. I would certainly recommend giving this a go to see if it achieves what you are after? Either wY, I look forward to continuing this conversation to find a resolution. Some amazing talent in this thread in @smpa01 and @AllisonKennedy.
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@TheoC , yes thanks a lot! Its always nice to see different alternative ways. Seemingly there are plenty of them in DAX...
@Applicable88 are you able to provide the measure that is giving you "50" across everything?
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |