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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Applicable88
Impactful Individual
Impactful Individual

Sum values in a n:1:n relationshop without possible usage of related()

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. 

2 ACCEPTED SOLUTIONS
AllisonKennedy
Super User
Super User

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

 

AllisonKennedy_0-1633062357042.png

AllisonKennedy_1-1633062444516.png

 

 

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. 

 

Process All, Time 2 =
SUMX(FactTable,
CALCULATE(
SUMX(NewFilterTable
, CALCULATE( SUMX(ManufacturingTime
, ManufacturingTime[Time2]
)
)
)
))
 
Process 20, Time 1 =
SUMX(FactTable,
CALCULATE( 
SUMX(
NewFilterTable
,
CALCULATE( SUMX(
FILTER(ManufacturingTime
, ManufacturingTime[ProcessCode] = 20
)
,ManufacturingTime[Time1]
)
)
)
))
 
Note: I am using a 'NewFilterTable' that includes ALL Materials, simply because that's again good practice. Measure should still work without it, but I don't recommend it. 
 
If you don't have a filter table with all materials, you can create one in Power Query - see the attached file where I right click on Fact table > Reference. Name the new Query: NewFilterTable. Then select only the Material column and remove duplicates. 
 

 

 


Please @mention me in your reply if you want a response.

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

View solution in original post

AllisonKennedy
Super User
Super User

@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! 🙂 

https://youtu.be/0MYmW4WiuIo 


Please @mention me in your reply if you want a response.

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

View solution in original post

23 REPLIES 23
AllisonKennedy
Super User
Super User

@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! 🙂 

https://youtu.be/0MYmW4WiuIo 


Please @mention me in your reply if you want a response.

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!  🙂

 

smpa01
Super User
Super User

@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

 

 

smpa01_0-1633093836732.png

pbix is attached here

 

https://1drv.ms/u/s!AkrysYUHaNRvhcV23WX0LZIKtlCfpQ?e=xNzPcb

 

smpa01_0-1633097864611.png

 

 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Applicable88
Impactful Individual
Impactful Individual

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. 

AllisonKennedy
Super User
Super User

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

 

AllisonKennedy_0-1633062357042.png

AllisonKennedy_1-1633062444516.png

 

 

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. 

 

Process All, Time 2 =
SUMX(FactTable,
CALCULATE(
SUMX(NewFilterTable
, CALCULATE( SUMX(ManufacturingTime
, ManufacturingTime[Time2]
)
)
)
))
 
Process 20, Time 1 =
SUMX(FactTable,
CALCULATE( 
SUMX(
NewFilterTable
,
CALCULATE( SUMX(
FILTER(ManufacturingTime
, ManufacturingTime[ProcessCode] = 20
)
,ManufacturingTime[Time1]
)
)
)
))
 
Note: I am using a 'NewFilterTable' that includes ALL Materials, simply because that's again good practice. Measure should still work without it, but I don't recommend it. 
 
If you don't have a filter table with all materials, you can create one in Power Query - see the attached file where I right click on Fact table > Reference. Name the new Query: NewFilterTable. Then select only the Material column and remove duplicates. 
 

 

 


Please @mention me in your reply if you want a response.

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. 


Please @mention me in your reply if you want a response.

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: 

AllisonKennedy_0-1633064121650.png

 

 

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

 

AllisonKennedy_1-1633064459134.png

 

 

Here's a bigger screenshot of the differences between your screenshots and mine: 

 

AllisonKennedy_2-1633064883400.png

 

PS, loving the discussion!

 


Please @mention me in your reply if you want a response.

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":

Applicable88_1-1633067742867.png

only after selection of ONE material I get the right totals:

Applicable88_3-1633067811450.png

After adding only one more material I get the wrong totals:

 

Applicable88_5-1633067898241.png

 

same goes for selecting all materials:

Applicable88_6-1633068081217.png

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

@AllisonKennedy 

 

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.

 

TheoC_1-1633062833531.png

 

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


Please @mention me in your reply if you want a response.

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:

Applicable88_0-1633088638884.png

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: 

Applicable88_2-1633089542723.png

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:

PrepTimeOnlyCode40 = sumx(Facttable,sumx(FILTER(ManufacturingTime,ManufacturingTime[ProcessCode]="40"),[PrepTimeSum]))

 

ManuTimeOnlyCode30 = sumx(Facttable,(SUMX(FILTER(ManufacturingTime, ManufacturingTime[ProcessCode]= "30"),[ManuTimeSum])))
 
UPDATE: The aforementioned two measure need to be wrapped up with a calculate function. I haven't look precisely enough at the totals. After I rechecked my sample data, I luckily saw that the totals not matching up. We might see the right value in every row, but the totals can be wrong. As @AllisonKennedy mentioned that calculate function was the solution to get a context transition in the facttable. Please refer to her solution.  
 
These two measures manage to show the correct time with the right condition of ProcessCode.
Its correct even before and after selection and also in multi selection.
Again as @AllisonKennedy  pointed out the bidirectional crossfilter needs to be on, that this can work:
Applicable88_3-1633090295011.png

More Materialnumber selected:

Applicable88_5-1633090397482.png

 

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:

Applicable88_7-1633090763263.png

 

Applicable88_6-1633090725563.png

 

If someone still can check if there is another way without cross filtering, I have prepare the pbix here: 
Applicable88_8-1633091030522.png

 

Thanks for the great community here.
Best. 
 
 
 

@Applicable88 

 

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

 

AK_Why15_PrepTimeOnlyCode40 =
SUMX(Facttable,
CALCULATE(
SUMX(
FILTER(ManufacturingTime
, ManufacturingTime[ProcessCode] = "40"
)
, ManufacturingTime[PrepTime]
)
)
)
 
Without a proper Dimenion table, you need to add the FilterTable into the DAX context as well: 
 
AK_PrepTimeOnlyCode40 =
SUMX( Facttable,
 
CALCULATE(
SUMX(FilterTable,
 
CALCULATE(
SUMX(
FILTER(ManufacturingTime,
ManufacturingTime[ProcessCode] = "40"
),
ManufacturingTime[PrepTime]
)
)
)))

 

 

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!


Please @mention me in your reply if you want a response.

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

Applicable88
Impactful Individual
Impactful Individual

@TheoC , yes thanks a lot! Its always nice to see different alternative ways. Seemingly there are plenty of them in DAX...

TheoC
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.