Hello All,
I have a DAX query which uses Summerizecolumns with order by.
and the query is as below.
EVALUATE
SUMMARIZECOLUMNS(
Projects[Top_ProjectManager],
KEEPFILTERS( FILTER( ALL( Projects[Top_ProjectManager] ), NOT( ISBLANK( Projects[Top_ProjectManager] ))))
)
ORDER BY
Projects[Top_ProjectManager] ASC
the data it retuns as
| Top_ProjectManager |
| Abdullah, Qassim |
| Acree, Adam |
| Adam Knier |
| Adkins, Chad |
| Akbay, Volkan |
| Akers, Paul |
| Ali Keyvani |
| Ambariantz, Zachary |
| Andrew Eck |
| Andrew Jason Dobrozsi |
| Ankur Rathor |
| Antonios, Walid |
| Argo, Christopher |
| Arnold, John |
| Avellano, Michael |
| Baer, John |
| Banovic, Kevin |
| Barker, Kristine |
| Barnhill, William |
| Bates, Brian |
| Battles, Michael |
| Baxter, Paul |
| Beale, Judy |
| Behrens, Brian |
| Behrmann, Michelle |
| Benjamin Keith Liptak |
| Bennett, Richard |
| Bissaillon, Joseph |
| Blade Alexander Hauth |
| Blaskovich, Megan |
| Bobko, Sam |
| Boltz, Jay |
| Borowiec, Jeffrey |
| Bradshaw, Dianne |
| Brett Harmon |
| Breunig, Denise |
| Brown, Curtis |
| Brown, Douglas |
| Bryan Albert De Jesus |
| Bryan Scot Gregory |
| Burkey, Jacob |
| Butler, Ryan |
| Carl F Armanini |
| Carol Anne Lockhart |
| Cestnick, John |
| Christina Lynn Martinez |
| Christopher Raml |
| Chu, Fred |
| Chu, Ted |
| Clarkson, Harold |
| Cocanougher, John |
| Cole, Eric |
| Coles, Melissa |
| Conner, Mendi |
| Copeland, Edward |
| Crews, Ellen |
| Crystal Childress |
| Culbert, Christina |
| Dabney, Unwanna |
| Damjanovic, Dejan |
| Daniel Andres Rey |
| Daniel J Kuester |
| David A Feuer |
| David A. Barron |
| David M Dillow |
| David M Helter |
| David N Ayala |
| David S Rickard |
| Davis, George |
| De Beer, Lueann |
| De la Cruz, Isaac |
| Denny, Gary |
| Devisser, Case |
| Diane Schlemmer |
| DiPilla, Peter |
| Dobrozsi, James |
| Dominic Vivian Hilton Thomas |
| Donna Pauline Taylor |
| Doug Garvey |
| Douglas S Richter |
| Douglas, Amanda |
| Downey, Jonathan |
| Drew W Linck |
| Dudley Smith |
| Dushan Arumugam |
| Dyer, Gregory |
| Einspahr, Morgan |
| Elam, Preston |
| Elder, Jill |
| Eli Donaldson |
| Eric Adam Jesse |
| Erickson, Jesse |
| Esfahani, Marjan |
| Esmaeili, Nasim |
| Etherington, Andrew |
| Fiore, Paul |
| Fischer, Carla |
| Fischer, Nathan |
| Fletcher, Hilary |
| Fluhr, Marisa |
| Ford, Michael |
| Foster, Brian |
| Fox, Gregory |
| Frank Monastra |
| Frank Simpson Orr |
| Fritsche, Barb |
| Garcia, Heather |
| Gary Edward Murray |
| Gauron, Trevor |
| Geboy, Jill Mackenzie |
| Gerdeman, Richard |
| Gerhard, John |
| Giessing, Chris |
| Gilbreath, Matthew |
| Gillespie, Matthew |
| Godfrey, Steven |
| Goldberg, Lawrence |
| Golombek, Yaneev |
| Gonzales, Benito |
| Goodell, Jessica#Jessica |
| Goodnight, Craig |
| Gotschall, David |
| Gregory D Harnish |
| Griesel, Dirk |
| Grohol, Morgan |
| Gromak, Natasha |
| Gross, Amanda |
| Gwin, Thomas |
| Hale, Timothy |
| Hall, David |
| Hamilton, Ean |
| Hamilton, Eric |
| Hammond, Benjamin |
| Hanowsky, Michael |
| Harding, Michael |
| Hartley, Chad |
| Heid, Joshua |
| Hensley, Darius |
| Herndon, Emily |
| Heydenrych, Riaan |
| Hocker, Benjamin |
| Hogan, Jeffery |
| Holbrook, K. |
| Holder, William |
| Hopper, Nathan |
| Hug, Justin |
| Hughes, Scott |
| Humphrey, Mark |
| Hunt, Cody |
| Hurst, Dane |
| Hutchinson, Matthew |
| Ingram, John |
| Isenberg, Nicholas |
| J. Todd Henwood |
| Jaeger, Chris |
| James A. Goggan |
| James P Kendall |
| Jason Adam Golub |
| Jeffrey Pierce |
| Jennifer Davis |
| Jeremy Stewart |
| Jesse Blackstock |
| John B Tye |
| John E. Schooler |
| John M. Alberti |
| John Matthew Fuller |
| John R Nierzwicki |
| Johns, Jason |
| Johnson, Derek |
| Johnson, Matthew |
| Jonathan L Grzywa |
| Jonathan R Sheidler |
| Joseph Anthony Margio |
| Joseph M Surra |
| Kaderka, Darrell |
| Kail, Jason |
| Kampe, Sean |
| Katie R Thayer |
| Keagy, Richard |
| Kelly, Margaret |
| Kent Park |
| Kevin K Shirer |
| King, Matthew |
| Kirsten Tynch |
| Knolle, Rebecca |
| Kohlbacher, Kathryn |
| Koonce, Laura |
| Kouns, Jennifer |
| Kowalkowski, Nicholas |
| Kreger, Stephen |
| Kurtz, Seth |
| Kuxhausen, Daniel |
| Kuxhausen, David |
| Leary, Timothy |
| Leitch, Robert |
| Lesko, Ian |
| Less, Thomas |
| Lickert, Mary |
| Little, Wilbur |
| Loker, Megan |
| Long, Michael |
| Lovato, Mark |
| Lucas, Adam |
| Ludwig, Christopher |
| Luiz Frediani |
| Maas, Daniel |
| MacDonald, Eric |
| Mackie, Thomas |
| Manderson, Kyle |
| Mark Aaron Mockus |
| Mark Guthrel |
| Martin, John |
| Matthew Churches |
| Matthew James Harrison |
| Mattox, Ronald |
| Mcclurkin, Joseph |
| Mccroskey, Ryan |
| McDaniel, Kirt |
| McDonald, Scott |
| McElfresh, Maureen |
| McGovern, Michael |
| McKoy, Jenine |
| Meade, Melissa |
| Meggyesy, Danielle |
| Meiser, Michael |
| Mel C Leseberg |
| Melanie Chavarria |
| Menezes, Sheldon |
| Merce, Thomas |
| Michael Dennis Tegethoff |
| Michael John Zoltek |
| Michael Kirk Maynard |
| Michalec, Daniel |
| Michelle Lee Carroll |
| Miklas, James |
| Milliard, Michelle |
| Millinor, William |
| Mochty, Thomas |
| Moffat, Samuel |
| Mohiuddin, Golam |
| Monnig, John |
| Montgomery, Jason |
| Moore, Shawn |
| Morgan, Jeffrey |
| Morgan, Timothy |
| Mosaffa, Amir |
| Muller, Crystal |
| Muscarella, Carla |
| Naicker, Kamashnee |
| Naicker, Kirsty |
| Ness, Justin |
| Nielsen, Kirk |
| Nix, Ray |
| Noble, Jonathan |
| Nodich, Alex |
| Ortiz, Sandra |
| Overton, James |
| Pack, Andrew |
| Paden, Bret |
| Parr, Christina |
| Pathakamuri, Bhargavi |
| Paul Strack |
| Perry, Christopher |
| Perry, Lauren |
| Pesler, Jeffery |
| Pestka, Joe |
| Phillips, Mary |
| Pickford, Andrew |
| Pieterse, Jacques |
| Pillay, Nivara |
| Pisana, Arthur |
| Plews, Patrick |
| R. Scott Schmidt |
| Rank, Melissa |
| Raymond, Benjamin |
| Reber, Tim |
| Remstad, Andrew |
| Rhoades, Timothy |
| Richard E Harrison |
| Riddle, James |
| Risner, Eric |
| Robert F Brinkman |
| Robert K Veech |
| Robert Scott Sanders |
| Rousseau, Booye#Booye |
| Roy Joseph Interrante |
| Ryan Alan Robinson |
| Sabourin, Julie |
| Sadeghi, Sam |
| Salazar, Tracey |
| Salinas, Julio |
| Sanfiel, Jose |
| Scherr, Kevin |
| Schneider, Cody |
| Schreuder, Ethan |
| Seamster, Emily |
| Self, Kenneth |
| Seppi, Joseph |
| Shamonsky, Karen |
| Sharma, Prateek |
| Sherilyn R Kosmos |
| Shillington, Mark |
| Shivprakash Iyer |
| Short, Anne |
| Shrestha, Prakash |
| Shuman, Zachary |
| Shuttleworth, Gregory |
| Siney, Ronald |
| Singer, Edward |
| Smallwood, Brian |
| Smith, Bryan |
| Smith, Paul |
| Smits, Mark |
| Snyder, Christopher |
| Solemani, Constance |
| Spinks, Melvin |
| Sprague, Jessica |
| Stedman, Kelly |
| Steele, Walter |
| Stelios M Xystros |
| Stephen D Tolison |
| Steven L Nixon |
| Steven W Schwabe |
| Steven Ward McCollum |
| Stevens, Brian |
| Strelitz, Emily |
| Tabb, Danielle |
| Thomas Ruschkewicz |
| Tilly Pillay |
| Timko, Michael |
| Todd Duwel |
| Todd M Andrews |
| Tolison, James |
| Tomber, David |
| Tomczyk, Mark |
| Tongay, William |
| Townley, Jennifer |
| Travis Ryan Davis |
| Tyler Bicknell |
| Undercoffer, Jason |
| Vallin, Travis |
| Vanderwalt, Barry |
| Vaughn, Roy |
| Virzi, Jason |
| Voisard, Robert |
| Walker, William |
| Wall, David |
| Walter, Ryan |
| Washington, Richard |
| Weeks, Jon |
| Wehmeyer, Andre |
| Welling, David |
| Wess, Ley |
| Wiley, Jonathan |
| Wilhelmi, Nathan |
| William Gunkle |
| William Howard Sukenik |
| William Powell |
| William Speck |
| William W Schwegler |
| Williams, Anthony |
| Williams, Daisylyn |
| Williams, Kelly |
| Wilson, Michael |
| Wilson, Nathan |
| Wimberley, Christopher |
| Wolf, Samuel |
| Worthy, Steven#Matt |
| Yates, Scott |
| Zachary Valchar |
| Zarlengo, Michael |
| Ziegman, David |
| Zumwald, Joseph |
Now i would like add a value as NA to the column and do union of both the above query and the new value.
So i have written below DAX.
EVALUATE
UNION
(
DISTINCT(SELECTCOLUMNS(Projects,"Top_ProjectManager","NA"))
,
SUMMARIZECOLUMNS(
Projects[Top_ProjectManager],
KEEPFILTERS( FILTER( ALL( Projects[Top_ProjectManager] ), NOT( ISBLANK( Projects[Top_ProjectManager] ))))
)
//ORDER BY
//Projects[Top_ProjectManager] ASC
)
With this i am able to add NA as new row vlaue but the thing i am loosing the order of the summerizecolumns query in it.
So the final output should be, NA should be top, then followed by the Order by on top_projectmanager is asc order.
Can anyone help me on this.
Thanks,
Mohan V.
