Forum Discussion

pedroccamaraDBI's avatar
pedroccamaraDBI
Icon for Post Partisan rankPost Partisan
4 years ago
Solved

TREATAS with 2 conditions same table

Hello everyone, My budget measure with treatas is not working and i don't have enough knowledge to understand why. Besides your help, which is always amazing, could you explain me why? So, among o...
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    Hi,

    The problem is in your time group columns. The values in the two tables don't match.

    In Sales you have values: 

    Whereas in Budget you have:

     

    So when treatas brings the filter over from Sales to budget nothing matches and you get a blank result.

littlemojopuppy's avatar
littlemojopuppy
Icon for Community Champion rankCommunity Champion
4 years ago

Hi pedroccamaraDBI 

Curious about your data model because this seems harder than it ought to be.  Your description says

  • my sales table has Date, Family, TimeGroup and Net sales
  • My budget table, which has been done by day has the Date column, Family, Time Group and Budget value

Do you have a date table and it marked appropriately?  And then relationships from the date table to date in the Budget table and MM-YYYY as a field in the date table and related to the sales table?  Because if you have those things (along with a proper dimension table for Family) this should be easy peasy.  Your measures for this should be simple.

 

Sales = SUM(SalesTable[Sales])

Budget = SUM(BudgetTable[Budget])

 

Filter context should do everything else for you.  I think your data model is off, which is creating a bunch of unnecessary complexity for you.

 

Would it be possible to provide a pbix with your data model?

bcdobbs's avatar
bcdobbs
Icon for Community Champion rankCommunity Champion
4 years ago

Just seen this. Fully agree with littlemojopuppy 

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    4 years ago

    bcdobbs great minds think alike 😉

    • littlemojopuppy's avatar
      littlemojopuppy
      Icon for Community Champion rankCommunity Champion
      4 years ago

      pedroccamaraDBI if you look at the pic you posted in response to the article I offered, it's clear that Power BI can't determine the appropriate time period for budget.  Try reworking your data model.  You should have four tables:

      1. Date table (must include date and MM-YYYY for each record)
      2. Family dimension
      3. Sales.  Create relationships between MM-YYYY and the date table AND Family and the Family dimension.
      4. Budget.  Create relationships between date and the date table AND Family and the Family dimension.

      From there should be really easy.  Use the two measures provided earlier for Sales and Budget.  Create your matrix using fields from the date table and Family dimension on the axes.  And you should be golden!

       

      Hope this helps!