Read slicer values, retrieve corresponding table column values and use them to check existence
Hi all, I am trying to retrieve a column values based on the selected single/multiple slicer values and use those data to check their existence in the other table. Let me put this out clearly. There are two tables i.e., Calendar and Employee data. The calendar has the following columns i.e., fiscal year, fiscal year period, date. Adding the screenshot below. Calendar table: The Employee table has Name, Location, Joined Date. Employee table: The slicer in the Power BI dashboard is added with the 'Fiscal year period' column from 'Calendar' table. If the user selects a particular fiscal period or multiple fiscal periods, the corresponding dates from the Calendar table have to be read in a dax calculation and check the existence of those date values in the Joined date column in the table 'Employee'. For instance, if the user selects 2025 P3 and 2025 P4 from the slicer, then the corresponding dates from 3rd March to 4th May have to be read and check if any of those values exist in the column from other table i.e., in Joined date from Employee table. Could you please help me with a dax calculation/measures for this scenario? Any suggestions will be appreciated. Thanks! Microsoft EnterpriseDNA
Sorting Data in Different Order Based on Slicer Selection in Power bi
Hi Everyone, I want to sort the data in a specific order. When I select a value in the slicer. Below is the raw data: Table : Year Month Product Amount 2023 1 AA 100 2023 2 BB 200 2023 3 CC 100 2023 4 DD 100 2023 5 EE 300 2024 1 AA 300 2024 2 CC 500 2024 4 DD 600 2024 5 ZZ 300 2024 6 BB 300 slicer : order Required The order in which the products need to be sorted, when we select the year from slicer."Sorting Matrix Data Based on Slicer Selection
Hi Everyone, I want to sort the data in a matrix in a specific order. When I select a value in the slicer, Below is the raw data image : The matrix contains Product and Value [Measure]. Raw Data Slicer : The order in which the products need to be sorted on the year value selected in the slicer. Raw Data: Table Product Year Value P3 2023 100 P2 2023 200 P1 2023 300 P8 2023 40 P6 2023 100 P1 2024 200 P3 2024 300 P2 2024 40 P14 2024 100 P12 2024 200 slicer slicer Year 2023 2024 Order needed Order Required [ when I select year in Slicer ] 2023 2024 Order order P1 P1 P2 P2 P3 P3 P6 P12 P8 P14 BeaBFCalculating Active Employees from Most Recent Record
Hello, I am trying to analyze workforce metrics over time. I have a fact table that has a record for each time an employee file has changed. It has at least one record for every employee. This table includes unique employee ID, Start date, division, Term Date, Effective Date (when the record was created) and a calculated column of Record Order- which numbers the rows for each employee, with 1 being the first record, 2 being the second and so on. I also have dimension tables for Date, Org Structure (Division/Section) Demographics I need to count active employees at a point in time where Start date <= SelectedDate, TermDate is blank, or > SelectedDate. The issue I am running into is; if an employee has been active in two divisions at different times during the selected period, they are Distinct Counted in each division. This means that the overall organization numbers are accurate, BUT they will count as an employee in each division when that slicer is applied. I need to be able to use just the most recent record for each unique employee- as that most recent record will show their current location. Example Data: PS ID start date Division Term Date RecordOrder Eff Date 1 1/1/2020 A null 1 5/18/2020 1 1/1/2020 A null 2 5/19/2020 1 1/1/2020 B null 3 7/11/2020 1 1/1/2020 B null 4 10/3/2020 2 1/2/2020 A 4/2/2021 1 1/2/2020 2 1/2/2020 A 4/2/2021 2 10/3/2020 3 1/1/2020 A null 1 1/1/2020 3 1/1/2020 B null 2 10/3/2020 In the above data set, using a date slicer on say 10/4/2020, I would want to return a distinct count (3) active employees, 2 in division B. 1 1/1/2020 B null 4 10/3/2020 2 1/2/2020 A 4/2/2021 2 10/3/2020 3 1/1/2020 B null 2 10/3/2020 The first approach I took was to use an 'events in progress' methodology 1. Measure to count all employees based on Start Date 2. Measure to distinct count all active employees at point in time where start date<= selected date, and term date is blank or after Selected date this returns accurate counts for the whole organization- but when appling division slicers, it counts employees as a member of every division they had been active in at any point. So, if someone was in A and later B- they would show up when slicing by A or B. I need to return only the most recent record. The next approach I tried was to identify the MaxRecordOrder of each ID, and filter the active employees measure where RecordOrder=MaxRecord Order: MaxRecordOrder = CALCULATE( MAX(Employee_Fact[RecordOrder]), ALLEXCEPT(Employee_Fact, Employee_Fact[PS ID], 'DateTable') ) Active Employees= VAR EndDatePerVisual = MAX('DateTable'[Date]) VAR RESULT = CALCULATE( DISTINCTCOUNT(Employee_Fact[PS ID]), REMOVEFILTERS('DateTable'), FILTER( Employee_Fact, Employee_Fact[Start Date] <= EndDatePerVisual && ( Employee_Fact[Term Date] > EndDatePerVisual || ISBLANK(Employee_Fact[Term Date]) ) && Employee_Fact[RecordOrder] = [MaxRecordOrder] ) ) RETURN RESULT This approach seems to correctly identify the max RecordOrder for each ID, responsive to the date slicer- but still counts rows where the MaxRecord order <> RecordOrder- so employees are still showing up in counts for multiple divisions! I've also attempted the second approach using the max effective date in place of the RecordOrder calculated column, with no success. Any help would be appreciated!
DAX and external filters
Hello, I have a small dataset (for training) using the following data model : I would like to calculate the percentage of sales (quantity) for any country / city related to global sales for Germany. So I used the following expression : measure15 = var currentSales = sum(sales[quantity]) var salesGermany = CALCULATE(SUM(sales[quantity]),ALLSELECTED(clients[city]),clients[country] = "Germany") return divide(currentSales,salesGermany) The measure works properly as long as the field clients[city] isn't filtered : case1 : case2 : case 3: I find th 3rd result a bit puzzling, could you kindly provide an explanation (why the part salesGermany is blank) ? Thanks in advance,
Writing measure to show multiple visuals on the same chart with one slicer
Hello, I am sorry to keep posting looking for a solution on this. I am trying to write a measure. I have tried multiple methods but cant get anything to work. I would assume having two different measures on the same visual would work. I have two tables (Table1 and Table2). My goal is to use one slicer and show multiple visuals from the same table on the same chart. For example, if a month is selected, I want to show data (filtered with different conditions) for that month, and the month most recent to the selected month. Column 'Table1'[Month] is linked to Column 'Table2'[Month] with a one to many relationship (Table2-one, Table1-many). Then I have a slicer that uses column 'Table2'[Month]. The visual for my current month works, but now I am trying the add the data to the same visual for its previous month. For the previous month, If the user selects a month with the slicer, I want to save the [Previous Month] associated in Table2 column as text. So if 24-Jun is selected, I want to save "24-May" as text. Then I want to filter and count the number of rows in Table1 where [Month End] = the saved text & Type = "Task". So in this example, I would count the number of rows in Table1 where Table1[Month End] = "24-May" & Type = "Task", and this would return 1. Table1 ID Status Date Rank Previous Status Month Type Group Previous Month A Normal 3 No previous 24-Mar Task <0 No previous B Normal 3 No previous 24-Mar Task 1 to 5 No previous C Not Normal 3 No previous 24-Mar Task 1 to 5 No previous D Not Normal 3 No Previous 24-Mar Not Task 11 to 15 No previous A Not Normal 2 Normal 24-May Task 6 to 10 24-Mar B Normal 2 Normal 24-May Not Task 6 to 10 24-Mar C Normal 2 Not Normal 24-May Not Task 6 to 10 24-Mar D Not Normal 2 Not Normal 24-May Task 6 to 10 24-Mar A Not Normal 1 Not Normal 24-Jun Task 1 to 5 24-May B Not Normal 1 Normal 24-Jun Task <0 24-May D Normal 1 Normal 24-Jun Task <0 24-May E Normal 1 24-Jun Task 1 to 5 24-May Table2 Date Rank Month Next Date Rank Previous Month 3 24-Mar 4 2 24-May 3 24-Mar 1 24-Jun 2 24-May Sample of visual I'm trying to create: Measure that doesn't work for previous month: (I keep getting blank when selecting a current month) VAR SelectedMonth = SELECTEDVALUE('Table2'[Month]) VAR PreviousMonth1 = CALCULATE( MAX('Table2'[Previous Month]), FILTER( ALL('Table2'), 'Table2'[Month] = SelectedMonth ) ) VAR CountRows1 = CALCULATETABLE( 'Table1', FILTER( ALLSELECTED('Table1'), 'Table1'[Month] = PreviousMonth1 && 'Table1'[Type] = "Task" ) ) RETURN COUNTROWS(CountRows1) Measure works for selected month: VAR SelectedMonth = CALCULATE( MAX('Table1'[Date Rank]), FILTER( ALL('Table1'), 'Table1'[Month] = SELECTEDVALUE('Table2'[Month]) ) ) VAR _tablecalculation = CALCULATETABLE( 'Table1', ALLSELECTED('Table2'), 'Table1'[Date Rank] = SelectedMonth, 'Table1'[Status] = "Not Normal", 'Table1'[Previous Status] = "Not Normal", 'Table1'[Type] = "Task" ) RETURN COUNTROWS(_tablecalculation) Any help in the right direction is definetly appreciated!Filter Slicer based on value on another slicer seems not work on me (a bug?)
Hello Power BI community, So here is the situation, I wanted to filter the date so that if I choose either "MTD" or "YTD", the date filter will be turned off. Only when I select "Period" will the date filter can be used. I followed some YouTube tutorials like: or and they provide a quite straightforward way to do it. So, what I did is I created a measure as follow: CustomDateDisable = INT(SELECTEDVALUE('MTD or YTD'[MTDORYTDORPERIOD])="Period") The measure seemed to work well for me. Proved by the value of the CustomDateDisable card: The problem came when I tried to put the CustomDatefilter measure on Date filter. The date filter seems to ignored the MTDORYTDORPERIOD value and only depends on how I choose the value on the CustomDateDisable pane. I really appreciate if there is someone to correct my logic or the implementation to this case. Thank you in advance. [UPDATE] Hey Power BI community, I update the case for an important context. It seems that I encountered some type of bug. When I changed the field to something other than Date, the filter worked perfectly to disabled the slicers. Also, the filter still worked fine when I changed the field to date, but the style I choose was vertical list or tile Only when I change the style to between, before, after, dropdown, relative date, or relative time, that the filter start to not working. Hopefully anyone on this forum can notice. Thank you in advance.
