Forum Replies Created
-
Posts
-
Hi Tom!
Hmmm… the TOPN(1, ) in ascending order of the TOPN(3, ). I wouldn’t have thought of that. 🙂
Attached is a workbook with sample data and the solution I came up with. There are two measures included. One specifically calculates the 3rd top salesperson. The other measure is essentially the same with the exception that it takes advantage of a disconnected table for use in a pivot table.
Ron
Ooops… I noticed there was some html in the above example, plus a missing CALCULATE(). Here’s the final final:
ThirdPlaceSalesperson:=MINX(
FILTER(
VALUES(Dim_Salespersons[FullName]),
RANKX(VALUES(Dim_Salespersons[FullName]),CALCULATE(SUM(Fact_Transactions[Revenue]))) = 3
),
CALCULATE(
IF(
HASONEVALUE(Dim_Salespersons[FullName]),
VALUES(Dim_Salespersons[FullName]),
COUNTROWS(VALUES(Dim_Salespersons[FullName]))
& ” Salespersons Tied”
)
)
)Well that was an interesting challenge for me. Below is the solution I came up with to find the nth top-performing salesperson. In the example, I want to determine the name the 3rd place salesperson.
ThirdPlaceSalesperson:=MINX(
FILTER(
VALUES(Dim_Salespersons[FullName]),
RANKX(VALUES(Dim_Salespersons[FullName]),SUM(Fact_Transactions[Revenue])) = <span style=”text-decoration: underline;”>3</span>
),
CALCULATE(
IF(
HASONEVALUE(Dim_Salespersons[FullName]),
VALUES(Dim_Salespersons[FullName]),
COUNTROWS(VALUES(Dim_Salespersons[FullName]))
& ” Salespersons Tied”
)
)
)Hope this helps others,
Ron
Hi Tom,
Thank you. Truly impressive what you know and what you did.
Yes, I will be signing up for the Power Query course. 🙂
Thank you!!
Ron
Hi BDeuce14,
Quick question: Is the data set static or do you need to be updating it from an external source (e.g. API, transaction database)?
I had a similar issue with a download from Yelp. In the table’s “Category” column each record was essentially an array of values. I used Excel to parse out the column’s records into a of list unique values. Then, created a “key” column. See the attached workbook, TestYelpData.xlsm.
Consider the following list of restaurant businesses and their associated category values:
– Restaurant 1: Japanese
– Restaurant 2: Chinese
– Restaurant 3: Bar
– Restaurant 4: Japanese,Bar
– Restaurant 5: Japanese,Chinese,BarIf the user or report selects “Japanese”, then restaurants 1, 4, and 5 should be included. If both “Japanese” and “Bar” are the selected options, then restaurants 4 and 5 should be included.
I’m trying to work out the filtering logic and how it might work.
– A category key was created for each business based upon the values in its Category column. The key is a simple concatenation of 0’s and 1’s. Since the edited category values are sorted, each value has a specific position within the key. For example, say “Bar” has the 2nd key position and “Japanese” holds the 5th position. Then a business with the category values of “Japanese,Bar” has a category key of “01001”. (The business’ order of the comma-delimited category values doesn’t matter.)
– So, I know if the user or report wants “Bar” and “Japanese”, the Business table is simply filtered upon the CategoryKey where the 2nd position is “1” (for “Bar”) and the 5th position is “1” (for “Japanese”).I’m going to be submitting a forum post/question on this myself, but I thought at least the direction of the approach might be of help for you.
Ron
Hi Tom,
Great to hear from you, too!
Yes, I also studied the Italian articles on SUMMARIZE and SUMMARIZECOLLUMNS. The articles are a resource for understanding their functionality. What I found lacking as the incorporation of the functions as a table parameter within a DAX aggregation function.
Hmmm… would this make an interesting topic for a PowerPivotPro blog post?
Hi Tom!
Hmmm… I will give your idea a try.
Thanks!!
I found the issue to be related to a shared slicer among the pivot tables. Disconnected the slicer from the old tables temporarily and the new pivot table updates quickly.
After building out the new pivot table I’ll reconnect the slicer to all of the tables and see what happens.
Tom,
You reminded me of DAX Studio. Thanks!!
Ron
Hey,
I solved the issue about Excel as a data source. A Microsoft post indicated our Windows Server instance needed an update, that I found at https://www.microsoft.com/en-us/download/details.aspx?id=13255
I had installed the update on my local machine. What was required was the update installed on the Windows Server machine with SSAS Tabular instance.
Works fine now!
Hopefully, this thread will help someone else encountering the same challenge.
Ron
Thank you, Tom.
Looking through the post and searching around I found these two other posts helpful, too:
http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/
http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx
On Monday, I’ll see if they fix the issue.
Thanks again!
Hi Thomas,
Thank you (!) For your very thoughtful reply. 🙂
Another approach I took was to create 13 time-dimensioned measures in the d_Date table, which8ch were referenced in the RelativeMonth calculated column. Same results, or I should say, lack of results. Again, it appears the calculated column is unaware of the filter placed upon d_Date table by the slicer selection.
Unless someone more knowledgeable than I figures out a solution the only approach I see for a individual month results over a rolling 13-month period is the creation of 13 individual measures with incremental month offsets.
Of course, a different approach is to calculate the time dimensions within the Excel environment through CUBE functions.
My goal is to have time-dimensioned measures usable in a pivot table and easily called by a CUBE function or within a SSAS tabular model. Define once, reference many.
Oh well, guess it’s back to creating lots of measures.
Cheers,
Ron
