Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter with UNION

hello to all,

 

I created a unique table from 2 tables "BUDGprojects" and "REALprojects" / without duplicates, with the following DAX formula:

REF_REEL&BUDGProjects = DISTINCT(UNION(BUDGProjects, REELProjects)).

 

The resulting table has only one column, containing analytical accounts.

I would like to further improve this DAX formula by adding a filter: take only the cost accounts that start with F, J, G or E.

 

If any of you know how to modify this formula by taking only these 4 categories of accounts, I am interested in the syntax!

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Try this formula.

     

    Table = DISTINCT(UNION(FILTER(BUDGprojects,LEFT(BUDGprojects[column],1) in { "F","J","G","E"}),FILTER(BUDGprojects,LEFT(BUDGprojects[column],1) in { "F","J","G","E"})))

     

     

    Best Regards,

    Jay

2 Replies

  • sayaliredij's avatar
    sayaliredij
    Solution Sage
    5 years ago

    You can try following formula

     

    Newtable = DISTINCT(UNION(CALCULATETABLE(BUDGprojects,left(BUDGprojects[Account],1) in { "J","F","G","E"}) , CALCULATETABLE(REALprojects,left(REALprojects[Account],1) in { "J","F","G","E"})))
     
    Regards,
    Sayali 
     
    If this post helps, then please consider Accept it as the solution to help others find it more quickly.
     
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Try this formula.

     

    Table = DISTINCT(UNION(FILTER(BUDGprojects,LEFT(BUDGprojects[column],1) in { "F","J","G","E"}),FILTER(BUDGprojects,LEFT(BUDGprojects[column],1) in { "F","J","G","E"})))

     

     

    Best Regards,

    Jay