topic Re: TREATAS between two end values in DAX Commands and Tips

TREATAS between two end values

Anonymous — Fri, 31 Jan 2020 08:37:03 GMT

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

Re: TREATAS between two end values

JarroVGIT — Sat, 01 Feb 2020 08:37:05 GMT

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:

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:

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

Re: TREATAS between two end values

Anonymous — Sat, 01 Feb 2020 18:19:10 GMT

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.

Re: TREATAS between two end values

Anonymous — Mon, 03 Feb 2020 12:39:32 GMT

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

Re: TREATAS between two end values

JarroVGIT — Mon, 03 Feb 2020 17:17:44 GMT

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