Measure that filters a table to a single row and returns text from a specified column (Excel365)
Hi there I still have a lot to learn about DAX, and have become stuck trying to develop a measure that will filter down my source table to a single row by using values in two columns and return the (text) value from a different, specified column. I have two measures that filter a table and calculate the expected values for (a) the oldest date for an unresolved service ticket and (b) the corresponding number of days elapsed after that date, but what I am unable to do is to retrieve the text value from a column for the row that table is calculating the age and date from. I've been going around and around in circles researching and trying different functions for far too long and am super confised now. I would really appreciate some help! 🙂 Most of my attempts at developing a measure result with an error message advising ~ that multiple have been supplied where a single value was expected, but by now I have failed in dozens of different ways. I've knocked up a file with dummy data and measures for reference - hope this helps with understanding my request and developing a solution. The pivot table I'm working with is used to generate statistics that are consolidated and presented as part of a dynamic and interactive 'dashboard' on another sheet; it is the "SYS" values that I am trying to populate with this measure: The image below is from the sample file provided, with the column in pink manually created to illustrate the desired result. Thanks in advance for your help!🤗
One Slicer Selection Filters Another Slicer Selection
Hello Power BI Community, I have two columns which have their own slicers on the dashboard: AZURE_AUDIT_METRICS[Object] AZURE_AUDIT_METRICS[Segment] When a team member selects a certain Data Object I would like the Segment slicer to filter to a specific Segment automatically. One Object can have many Segments so we want to highlight (filter for) the most important Segment right away. This is best explained with an example.... The user selects Data Object slicer AZURE_AUDIT_METRICS[Object] = "PRA UNIT VENTURE". When this selection is made there are two Segment slicer AZURE_AUDIT_METRICS[Segment] selections available (see screenshot). We want the Segment slicer AZURE_AUDIT_METRICS[Segment] = "UNITVENTURE PUC" to be automatically selected by default when Data Object slicer AZURE_AUDIT_METRICS[Object] = "PRA UNIT VENTURE" is selected. Thank you so much for your assistance!! BrendenCreate Measure to be used for Slicer and Filter
I have Kwywords and URL in Site Link May/Jun table I have Unique Pages/Queries I have page and keywords performace in Clicks, CTR and rank in DS Page And Query Table Now I want to see URL which are appearing in sitelinks Logic to determine URL are in site link Step 1: Check the Keyword in Sitelink Jun/May table (if keywords is present in table then Yes otherwise Keywords did not have site link on SERP) Step 2: Find out URL that are in sitelink and URL that is on main ranking. (Multiple URLs ranking on same position and URL with highest CTR out of those is main URL, URLs other than highest CTR URL are links appearing in sitelink for the query) I want to create a Slicer Named With Sitelink or W/O sitelink whcih should give me clicks number for a URL from sitelink (sitelink can be for many queries)Help to create a Measure to group data by a fixed number of consecutive Rows
Hi everyone, I'm looking to create a Measure that allows me to group sales data by a fixed number of consecutive Rows - to calculate the sum of 12 months sales periods for a Product. For example, if a Product goes on sale in the middle of the year, and the first period of sales is 201706 (as in graph below), how do I calculate the Sum of Volume for the next 12 periods to make up the total 12 month sales period, and then the following 12 month periods? My aim is to have a graph that shows me: Y1: Total sales (Sum of Volume for 13 consecutive periods) Y2: Total sales (Sum of Volume for 13 consecutive periods) Y3: Total sales (Sum of Volume for 13 consecutive periods) Etc!
How to generate hours that are allocated based on date and machine
Hi all, May need your help in to solve an issue that i currently face . I am currently stuck and no idea if this is possible to do in PowerBI (currently doing it in excel and would like to automate the report ). I have a case where we need to monitor the loading for each machine that are assigned . So each job number will be having balance hours and days remaining for it to complete the job. I have created a calendar table that are connected to the job operation date. My main question is how do i generate hours (balancehours / days ) to get an output similar as below . (above is the expected out but in excel .. the chart is based on week , i would like by date so that I can monitor by weekly , month and year) I have few conditions also: -The hours divided must be within job start date (JobOper_StartDate) and end date (JobDue_Duedate) . -Job that are passed due date not need to generate and will go to backlog hours as shown in chart So the expected output : We are able to sum the total hours divided based on date , month and also based on machine(resource) . Here are sample data for factory loading : Calculated_BalanceHrs Days Job Num JobOper_Startdate JobOper_DueDate Resource 34.71 2 172008 28-Jun-23 27-Jun-23 CM4 20.93 4 172631 18-Sep-23 15-Sep-23 CM4 24.48 3 172632 07-Jul-23 05-Jul-23 CM3 32.78 3 172637 23-Jun-23 21-Jun-23 CM2 14.58 2 172684 14-Jul-23 13-Jul-23 CM2 6.38 2 172687 13-Jul-23 12-Jul-23 CM2 10.04 2 172694 07-Jul-23 06-Jul-23 CM2 35.18 6 172720 27-Jun-23 22-Jun-23 CM3 12.85 2 172809 16-Jun-23 15-Jun-23 CM4 6.79 2 172818 27-Jun-23 26-Jun-23 CM4 71.27 4 165294 24-Nov-23 21-Nov-23 CM1 36.76 5 168177 22-Aug-23 18-Aug-23 CM1 34.48 6 170292 06-Dec-23 01-Dec-23 CM1 34.48 2 170293 03-Nov-23 02-Nov-23 CM1 34.48 6 170294 04-Oct-23 29-Sep-23 CM1 34.48 6 170295 06-Sep-23 01-Sep-23 CM1 34.48 2 170296 04-Aug-23 03-Aug-23 CM1 14.7 5 170300 28-Nov-23 24-Nov-23 CM1 14.7 5 170302 26-Sep-23 22-Sep-23 CM1 14.7 5 170303 29-Aug-23 25-Aug-23 CM1 14.7 2 170304 28-Jul-23 27-Jul-23 CM1 14.7 5 170305 27-Jun-23 23-Jun-23 CM1 67.74 8 171880 18-Aug-23 11-Aug-23 CM2 130.44 15 171881 05-Oct-23 21-Sep-23 CM1 21.15 2 171883 28-Jul-23 27-Jul-23 CM1 40.65 7 171884 26-Sep-23 20-Sep-23 CM1 43.56 4 171885 30-Jun-23 27-Jun-23 CM4 91.16 10 171886 05-Oct-23 26-Sep-23 CM1 19.72 2 172139 03-Aug-23 02-Aug-23 CM4 19.72 2 172140 06-Jul-23 05-Jul-23 CM4 14.3 4 172143 10-Jul-23 07-Jul-23 CM4 1.8 2 172454 29-Jun-23 28-Jun-23 CM2 21.45 2 172524 19-Jul-23 18-Jul-23 CM1 24.84 2 172623 04-Jul-23 03-Jul-23 CM4 8.36 2 172627 13-Sep-23 12-Sep-23 CM4 8.36 2 172628 05-Jul-23 04-Jul-23 CM2 19.96 2 172629 14-Sep-23 13-Sep-23 CM4 19.96 2 172630 05-Jul-23 04-Jul-23 CM3 45.3 4 172681 14-Jul-23 11-Jul-23 CM3 4.39 4 172685 19-Jun-23 16-Jun-23 CM4 25.4 6 172711 04-Jul-23 29-Jun-23 CM3 22.95 3 172712 29-Jun-23 27-Jun-23 CM3 44.31 7 172713 21-Jun-23 15-Jun-23 CM2 3.55 1 166957 21-Jun-23 21-Jun-23 CM2 6.56 0 171533 16-May-23 16-May-23 CM1 4.48 1 172088 23-Jun-23 23-Jun-23 CM2 4.49 1 168032 29-Aug-23 29-Aug-23 CM1 Thanks in Advance.Power BI Desktop: Custom Aggregations, Formatting and Performance Indicators (Part 1)
This multi-part tutorial blog is geared to help BI Analysts create versatile Scorecards with KPIs as well as to facilitate the business reporting transition from Excel, SSRS or other apps to Power BI Desktop. In this initial installment, I will illustrate how to include more than one aggregation and custom formatting in the Power BI Desktop Matrix. All tutorial steps below require acquaintance with how Power BI Desktop works as well as basic to advanced DAX skills.
