TREATAS with 2 conditions same table

[pedroccamaraDBI]

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 other columns, 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.
The budget table is connected with dates table only. 
Now, i would like to see the values of sales, with any period of time, by Family and/or by Time Group. This is the measure:

TBudget =
CALCULATE (
      [NEW Budget],
      TREATAS (
           SUMMARIZE ( 'Tab Sales', 'Tab Sales'[Time Group], 'Tab Sales'[Family] ),
           'Tab Budget'[Time Group],
           'Tab Budget'[Family]
)
)
Can anyone help me please?
This is one of the visuals i want to achieve

Thank you all so much

[pedroccamaraDBI]

Hello friends @bcdobbs and @littlemojopuppy 
I've manage to do some changes in my data, but the result, the issue is the same.

Here is my file and thank you so much for all your help

[bcdobbs · Accepted solution]

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.

[pedroccamaraDBI]

Hello @bcdobbs 
This unbelieveble, unacceptable, "unmistakeble" ....how could i do such this error?
Well...thank you so much!😁

[bcdobbs]

Don't worry I''ve certainly been there looking for a mistake in code when it was my data all along.

[bcdobbs]

Hi @pedroccamaraDBI, At first glance your usage of TREATAS should work from a syntax point of view. I suspect there is some other interaction happening in your model or the [New Budget] measure. Can you ideally share a demo pbix file or the DAX for the [New Budget] measure?

[pedroccamaraDBI]

Hi @bcdobbs 
That one is a simple one 

NEW Budget = SUM('Tab Budget'[Budget] )

This is my budget table

Thank you for steping in

[bcdobbs]

Can you share what the main Tab Sales table looks like and also a picture of the model view?

[littlemojopuppy]

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?

[pedroccamaraDBI]

Hello @littlemojopuppy 
All checked and accordingly to everything. Date table is marked as a date table. I don't have the family column, nor Type group column as a dimension table because they're a part of sales table.
And my model right now is pretty simple, both my budget and sales table are conencted to dates properly.
In my opinion, the problem is that measure with TREATAS. If i change the column name time group from sales to budget, it shows me correctly the budget by any period and wrong sales. But if that column is from sales, then i have the sales ok but not the budget.
Maybe this measure should be done in another way. I don't know.
And i am very sorry to say but my data are a litle sensitive, at least for public. But i apreciate very much if you send me your email by message to me.
Thanks a lot for all your help
P.S i don't have a Family dimension table not a type group dimension table

[littlemojopuppy]

Hi @pedroccamaraDBI 

I think it would be rare for any of us to do this for fun (except maybe for an interest we might have such as sports).  The guys that work for me have instruction to look for as much help as they can get from people in this forum (including me, but they can always ask me at work) but to never, ever share confidential data because that's the kind of thing that gets people fired.  So I completely understand.  🙂

Let me try to convince you of this: take 100 records from each of your Sales and Budget tables and drop them into Excel.  Change details so you have family of "XYZ" or named after fruits (Apple, Banana, Orange, Mango, etc.)  Change the amounts too if you like.  Just include all columns from each table and I'll figure this out.

[littlemojopuppy]

I want to add that with people in the forum who ask "this is what I want go make it for me" I'm a kind of an ass to, because it's clear they're not trying.  You're different: you are trying!  So I'm willing at help you as much as possible 🙂

[bcdobbs]

Just seen this. Fully agree with @littlemojopuppy 

[littlemojopuppy]

@bcdobbs great minds think alike 😉

[littlemojopuppy]

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

[littlemojopuppy]

Hi @pedroccamaraDBI 

TREATAS is for taking a filter from one table and applying it to columns in another, unrelated table.  Here's an article from Radacad that seems to cover what you intend to do (it's about halfway down the page).

Hope this helps!

[pedroccamaraDBI]

Hi @littlemojopuppy  
Thank you for your answer. 
What i didn't understand is what we connect virtualy to. You know? For me i thought it would be sales with budget. But that measure didn't work and only by changing the order it ....almost worked

TBudget2 =
CALCULATE (
[NEW Budget],
TREATAS (
SELECTCOLUMNS (
'Tab Budget',
"Family", 'Tab Budget'[Family],
"Time Group", 'Tab Budget'[Time Group]),
'Tab Sales'[Family],
'Tab Sales'[Time Group])
)

I said almost because now i see the values but it doesn't "relate" with Time Group as you can see:

The numbers are right at the total but not on detail. And this is exactly what Reza did.
Any ideas?

[bcdobbs]

I think until we can see some more information as @littlemojopuppy has suggested it's almost impossible to help. Can you at least send an image of your data model?

I created a simple mock up with the code you've sent so far and the only way I could recreate your blanks was by spelling values in Time Group and Family differently in the Sales and budget tables. Are you sure they're identical? (Run a clean/trim on them in power query).

Your last post with the SELECTCOLUMNS just suggests something odd is going on somewhere in your setup.