TREATAS between two end values

[Anonymous]

Hi, 

Is there a way I can get my TREATAS function to take all the values in between the two valuse example between purpose1071 and 1116, without writting them individually?

Test = CALCULATE(

[Finance];

TREATAS({("1071..1116") ; ("1000..1005") ; ("1119..1258") ; ("1260..2098") ; ("2102..9999") ; ("1062..1066") ; ("1007..1060")};

Purpose22[Purpose Code]

))

Thankyou

[JarroVGIT · Accepted solution]

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

[Anonymous]

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

[JarroVGIT]

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

[Anonymous]

Why don't you use simple filter condtion

Calculate(measure,filter(table,table[column]>1071 &5 table[column]<1116))


For more conditions you can add or clause inside filter section.


Thanks
Pravin
If it resolves your problem mark it as a solution and give Kudos.