![]() With our amount measure on hand, we can create an easy DAX measure that uses CONCATENATE to bring together both of these values. We ask, and DAX can deliver!Ĭompared to our Top Purchasing Customer Measure, this one is much simpler! The measure looks at our calendar table, and then returns the value for the row (which is day on the calendar table). However what if I also wanted to know the DOLLAR AMOUNT associated with that sales day? Well friends, we can have our cake and eat it too in this scenario. We could use a variation of the above DAX Measure, this is true. Let’s say we had a scenario where we ALSO wanted to know what the Top Sales Day Amount was. ![]() Not only can you use DAX to return a Top Performing “thing”, we can also return the Top Value! Rob’s most recent post on top performers shows an easy way to return the value associated with the top performing thing. Simple right? Why Stop Here? Let’s Return The Top Value Too! The only purpose of this step is to convert a table to a single (scalar) value output. To be fair I’m arbitrarily using the MAX(X) function, but I could have just as easily used ANY of the X Functions (E.g. 3) X Functions to the rescue! Since we need a single value output we can use the MAXX to convert our table INTO a single (scalar) value, which for us is the Sales Day. However TOPN returns a table, and DAX measures can’t display a table…they require a single (scalar) value output. 2)We then use TOPN to return the row (Sales Day) with the highest order count from the SUMMARIZE virtual table. 1)The SUMMARIZE function returns a virtual summary table of Sales Days and. SUMMARIZE ( ‘Calendar’, Calendar, “Order Count”, ),Īs you can see there’s multiple layers to this DAX Measure, but I can assure you they all have a purpose! Let’s start from the inner most query and move our way out. Using the image above as the example, let’s take a look at the measure that uses the SUMMARIZE function. return only the first row with the top ). Instead TOPN can filter the table from SUMMARIZE using row-context (E.g. The beauty in using SUMMARIZE is that it allows us to avoid using the expensive FILTER function, which is performance hungry. However, Gerhard Brueckl provided a great follow up in the post comments showcasing a FASTER way to do this using SUMMARIZE. Rob’s second post originally used FIRSTNONBLANK() to return the same result. Today’s post will be using the SUMMARIZE method to return my Top Performing “Thing”. Rob’s post really helped open my eyes to the world of possibilities that DAX is capable of. I’m not sure about you, but when I was first learning DAX years back, I never considered using a “Math Engine” to return text outputs. ![]() In my opinion, showing top performers is a powerful (and useful!) DAX Measure. Summarize dax portable#The beautiful thing about Top Performing DAX Measures is that it’s portable & dynamic! Basically any way we slice and dice our data, will change with it, JUST like any other DAX Measure (E.g. Another Way To Find The Top Performing “Thing”ĭAX Can Return More Than Just Numbers Or Dates…. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |