User Profile
ahhk2000
Copper Contributor
Joined 4 years ago
User Widgets
Recent Discussions
DAX Measure works in Power BI, but not Excel Power Pivot
I have the following measure used in Power BI to count the number of client interactions within 12 months by the other consultants from a selected consultant's sector practice. I'm trying to use it in an identical Excel Power Pivot model (since this is the format I need to use to share with some work colleagues), but it seems I am stuck on an older version before operators such asSELECTEDVALUE and IN were introduced. I tried swapping in HASONEVALUE but end up with an error"Cannot find table 'Last12Months'. Any idea how to adapt the measure to work with Excel 2016 Version 16.x? Thank you. Peer Interactions = VAR SelectedConsultant = SELECTEDVALUE(Consultants[Consultant]) VAR SelectedSector = SELECTEDVALUE(Consultants[Sector]) VAR Last12Months = DATESBETWEEN(Dates[Date], TODAY()-365, TODAY()) RETURN CALCULATE( COUNTROWS(Records), FILTER( ALL(Consultants), Consultants[Sector] = SelectedSector && Consultants[Consultant] <> SelectedConsultant ), Records[Consultant] <> SelectedConsultant, Records[Activity Date] IN Last12Months )Help with a DAX measure to count based on latest dates
Grateful if someone can help me come up with a measure for such a scenario: The process for job promotion is that a group of voters periodically submit batches of votes for any employees at a company that they wish to support. This can happen at irregular intervals, with different voters voting at different times. Once the voter has voted for an employee, they are considered to support them unless a later batch of votes is received without any record for that employee. The table of vote records is called VoteHistory and has columns Voter / Employee / Submission Date. This is linked to a typical Date table. I want to be able to chart a time series for specific employees that will show the total Votes that they had received at different points in time. So at a certain point in time, the total votes would be determined by filtering the most recent batches of votes prior to that date (across all voters), then counting the number for that employee. Any suggestions on how to write something like this please?Changing a power query source
This seems very basic, but haven’t figured it out. I have an existing Power Query based on loading a table from a single Excel file. I want to replace the source with a folder containing multiple Excel files that should be combined. How can this be done? Or am I supposed to delete the whole query and its table from the data model, and then recreate?PowerPivot/DAX - comparison of one column to another column
Hi experts, I currently have a PowerPivot dashboard where I use measures to concatenate and return the names of clients who have received certains types of service: Call Recipients =calculate(CONCATENATEX(values(ServiceHistory[Contact Name]), ServiceHistory[Contact Name],“,“), ServiceHistory[Service Type]=“Call”) Meeting Recipients =calculate(CONCATENATEX(values(ServiceHistory[Contact Name]), ServiceHistory[Contact Name],“,“), ServiceHistory[Service Type]=“Meeting”) I'm trying to figure out how to set up a measure(s) to return the names of clients who have receive one type of service (Call), but have not received another type (Meeting). Any idea how this can be done? Thanks in advance for any advice!Power Pivot, slicer and table relationships
Really appreciate any advice here. I have a Power Pivot data model as shown below for a consulting business scenario. I have several measures such as: Distinct Activities = calculate(DISTINCTCOUNT(ServiceDetails[Activity ID])) Service Status = if([Distinct Activities]>=5,2,IF([Distinct Activities]=0,0,1)) The “Service Status” measure is used to set up conditional formatting icons to indicate the frequency of service. We recently surveyed sales people for feedback on which accounts (i.e. ServiceDetails[Master Group]) are relevant to be serviced by particular consultants (SalesFeedbackTable[Addressable?]). I want to set up a slicer based on this field to toggle the pivot table between views of addressable (Yes) and (No) accounts. In an attempt to do this, I used CROSSFILTER to create the measure: Service Status - Addressable = CALCULATE([Service Status], CROSSFILTER (EmployeeTeams[Consultant Name], SalesFeedbackTable[Consultant Name], Both)) Instead of just toggling the views between “Yes” and “No” accounts, the “Service Status” measure now outputs different values depending on the selection, with all rows always displayed. I’m sure I’ve misunderstood the use of CROSSFILTER… Any idea how to achieve what I’m trying to do?Data entry options for a table
First, Happy New Year! Hoping to get some process ideas from the community. Let's say I have a table of data for a sales division with sales people as rows and accounts as columns. The cells show sales totals for the past year. I want to circulate these tables to managers and seek their input on how sales people are performing against accounts. Ideally I'd like for them to be able to click on a cell and provide feedback on several criteria (e.g. above plan/meets plan/below plan, Prospect name, need introduction? (Y/N), etc.) and for this feedback to be captured in a convenient way to summarise & analyse. What is the best approach to set something like this up? Ideas appreciated...DAX time comparison function help
Would be grateful for advice on how to calculate the following with a DAX formula & Power Pivot (Excel 2016) I have a sheet withthree columns: Date (in DD/MM/YYYY) | Name of Sales Person | Sale Amount I'm trying to calculate the percentage change in total sale amount between two periods (for example last 6 months vs. prior 6 months). Any suggestions appreciated.Power Pivot Measure and Time Formula help
Hi everyone. Your advice is much appreciated... hope the situtation is clear (apologies that I cannot upload files from work environment). I have set up the following measure to count the number of distinct "Gold" tiered clients that different teams have provided consultation services to. =calculate(DISTINCTCOUNT(ServiceDetail[Client Name]), ServiceDetail[Activity Type]=“Consultation”,ClientInfo[Tier]=“Gold”) Building on this, I am trying to reference two other fields: ServiceDetail[Activity ID] ServiceDetail[Activity Date] in order to achieve the following: 1. I would like to count the number of Gold tiered clients that receive consultations on a regular basis. For example, at least 5 times in the past year. 2. I would like to exclude duplicates (based on Activity ID). Sometimes the same activity is replicated within the ServiceDetail table if multiple team members are involved. I'm struggling to figure this out, so would be grateful for pointers!
