Forum Discussion
TREATAS between two end values
Hi Anonymous ,
I haven't used TREATAS often, so had to figure out a way to meet your requirements. I like these type of questions because basically I learn a lot based on a real use case 🙂
Anyway, the solution comprises out of TREATAS, UNION and GENERATESERIES. The GENERATESERIES() function returns a one-column-table based on its parameters (start, end and interval(defaults to 1)) So, GENERATESERIES(1,4) will create a table like this:
1 2 3 4 UNION() takes two or more tables as input and appends them. So, if we use these together like this:
UNION(GENERATESERIES(1, 3), GENERATESERIES(8, 10))The resulting returned table is:
1 2 3 8 9 10 Now, combining this in your case, leads to the following DAX:
Test = CALCULATE( [Finance]; TREATAS( UNION(GENERATESERIES(1071; 1116); GENERATESERIES(1000; 1005); GENERATESERIES(1119; 1258); GENERATESERIES(1260 ;2098); GENERATESERIES(2102; 9999); GENERATESERIES(1062; 1066); GENERATESERIES(1007; 1060); Purpose22[Purpose Code]) ))Let me know if this solves your issue 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Hi Anonymous ,
I haven't used TREATAS often, so had to figure out a way to meet your requirements. I like these type of questions because basically I learn a lot based on a real use case 🙂
Anyway, the solution comprises out of TREATAS, UNION and GENERATESERIES. The GENERATESERIES() function returns a one-column-table based on its parameters (start, end and interval(defaults to 1)) So, GENERATESERIES(1,4) will create a table like this:
| 1 |
| 2 |
| 3 |
| 4 |
UNION() takes two or more tables as input and appends them. So, if we use these together like this:
UNION(GENERATESERIES(1, 3), GENERATESERIES(8, 10))
The resulting returned table is:
| 1 |
| 2 |
| 3 |
| 8 |
| 9 |
| 10 |
Now, combining this in your case, leads to the following DAX:
Test = CALCULATE(
[Finance];
TREATAS(
UNION(GENERATESERIES(1071; 1116); GENERATESERIES(1000; 1005); GENERATESERIES(1119; 1258); GENERATESERIES(1260 ;2098); GENERATESERIES(2102; 9999); GENERATESERIES(1062; 1066); GENERATESERIES(1007; 1060);
Purpose22[Purpose Code])
))
Let me know if this solves your issue 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Hi JarroVGIT thanks for the suggestion, but my code does noet return any values.
Do you see what i am missing? 🙂
TEST= CALCULATE(
[Finance Actual Amount];
TREATAS(
UNION(GENERATESERIES(1071;1116;1071); GENERATESERIES(1000;1005); GENERATESERIES(1119;1258); GENERATESERIES(1260 ;2098); GENERATESERIES(2102;9999); GENERATESERIES(1062;1066); GENERATESERIES(1007;1060));Purpose[Purpose Code]))
Kind regards Søren
I re-ran my test to double check if what I said was true, but I should've looked closer at your DAX 😛
The first GENERATESERIES has 3 arguments. Else this should be working or there is another filter active (slicer?)
I retested it with this dataset:
Name | ID
A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 10 with measure:
Measure 2 = CALCULATE(DISTINCTCOUNT(Table2[Name]), TREATAS(UNION(GENERATESERIES(1,4), GENERATESERIES(8,10)), Table2[ID]))This returns 7 as I expect it would.
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂