Forum Discussion
Post PartisanTREATAS 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 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
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.
17 Replies
- bcdobbs
Community Champion
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?
pedroccamaraDBIHi bcdobbs
That one is a simple oneNEW Budget = SUM('Tab Budget'[Budget] )This is my budget tableThank 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
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.
- 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
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!😁
