Forum Discussion

ergocorp's avatar
ergocorp
Helper II
2 years ago

Using another table's values in calculate

Hi, I am hoping someone can help. We are trying something with my company's cash forecasting.   We get a report every week that lists every invoice that has come in on the ledger. These willl have ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

The relationship is on the column 'C Week of Report' in the relationship, vs. on 'C Adj Week' in the measure, so there is indeed no relationship between those two columns. Can you adjust the relationship or the measure (depending on which is appropriate)?

  • ergocorp's avatar
    ergocorp
    Helper II
    2 years ago

    I may be able to change it to the adjustment column. I'll have to check tomorrow (I've logged off now. - it's 5pm here). Thanks 

  • ergocorp's avatar
    ergocorp
    Helper II
    2 years ago

    I couldn't help myself, I logged on to check while I have ordered a delivery lol. That didn't work, both changing the column relationship:

     

    Nor reverting that and changing the measure:

    Reversing the relationship isn't allowed as it causes an ambiguous path to another table, and changing the cardinality isn't allowed either.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi ergocorp ,

      Did you create any inactive relationship between the table SAP[C Week Of Report] and Week[Week Number]? Please update the formula of measure [M1wx] as below and check if it can return your expected result...

      M1wx =
CALCULATE (
    [M Total],
    FILTER ( SAP, SAP[C Week Of Report] = MAX ( Week[Week Number] ) + 1 )
)

      If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format, expected result and your visual settings. It would be helpful to find out the solution. You can refer the following link to share the required info:

      How to provide sample data in the Power BI Forum

       

      And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

      How to upload PBI in Community

      Best Regards

      • ergocorp's avatar
        ergocorp
        Helper II
        2 years ago

        Thanks, it hasn't worked. I don't actually believe what I want to show is acctually correct either.

         

        I can't create and attach an example - this is extremely bespoke, I'm not sure how I would trim it down and change things to not show sensitive data.