Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

TREATAS between two end values

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
Help required
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago

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

4 Replies

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        JarroVGIT
        Resident Rockstar
        6 years ago

        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

        A1
        B2
        C3
        D4
        E5
        F6
        G7
        H8
        I9
        J10

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