Filtering from slicer and OR logic in DAX is not able to achieve in my case
Hi All, Data Validation : I am using 2 slicers (Amount in usd & churn in usd) with min & max values and few cards to display the counts based on the selected slicer values as below : below the query which is executed from snowflake data source and the expected result is 490 , but i am getting 447 with all the filters applied in this query (using slicers in power bi report) Snowflake query : Output - 490 row count select * from datalake.dm_bss_oss.vw_opportunity_quote_source where sales_involvment='Sales Opportunities' and opportunity_stage='Closed Won' and closed_date between '2025-01-01'and'2025-06-30' and (amount_in_usd between '0' and '50000' or churn_in_usd between '-50000' and '0'); -- values 0,50000 and all are just a sample in this query but we have a separate slicers for the amount seelction in dashboard DAX query : Output - 447 row count (should be 490) CALCULATE (COUNT('Opportunity Quote Source'[SFDC_ENSONO_OPPORTUNITY]), FILTER (ALLSELECTED('Opportunity Quote Source'), ( 'Opportunity Quote Source'[AMOUNT_IN_USD] >= MIN('Opportunity Quote Source'[AMOUNT_IN_USD]) && 'Opportunity Quote Source'[AMOUNT_IN_USD] <= MAX('Opportunity Quote Source'[AMOUNT_IN_USD]) ) || ( 'Opportunity Quote Source'[CHURN_IN_USD] >= MIN('Opportunity Quote Source'[CHURN_IN_USD]) && 'Opportunity Quote Source'[CHURN_IN_USD] <= MAX('Opportunity Quote Source'[CHURN_IN_USD]) )))
Dynamic measure filtering
Hi Community, I am trying to create a report with multiple slicers that filter the underlying table dynamically. Let me explain. I have an underlying sales by date table: Firstly, the user would select from the State and Date slicers. For example Then, I will have a third filter that have predefined Average Sales Band, e.g. 100-150, 150-200, 200-250, so on ... Lastly, I will then need to create a third slicer to filter the Average Sales by State and Product. I can not create a summarized/calculated table because the date/period will need to be dynamic based on the the Date slicer. What I have done is created a measured called Average Sales, which is just AVERAGE(SALES) and based on Slicer1 & Slicer2 (above) I am able to produce this results: What I next want to do is that in Slicer3 the predefined Average Sales Band and if the user select "100-150" then only return Banana and Oranges. How do I complete the last step?
Date Filters
I am trying to get a massive switch statement to filter dates dynamically and I do not know if this is possible. I have three main date calculations. CurrentDatePeriod,PriorDatePeriod, and AsOfDate. CurrentDatePeriod and PriorDatePeriod are booleans which look at the date comparison value and either say true or false depending if the calender date is in the selected time frame. For example, the value last month vs prior month has been chosen. The AsOfDate shows that 11/2/2024 is the end of the month. This logic works fine but i run into some issue when trying to get it to work with other data tables. First issue: I would like to be able to place the measure: currentDatePeriod or PriorDatePeriod on the filter view and set it to true or false but PowerBI does not seem to be able to handle this. Second Issue: How can I get this logic to work with my fact sales table so I can create calculations such as total_customers_CP = IF([currentDatePeriod],[total_customers],BLANK()). My sales table is not reacting to the date filter calculations. Data model: Do I need to make the date comparison table a parameter then add that to my currentdateperiod,priordateperiod,and asofdate calculations. let me know if you need further advice. I have pasted a link to an example workbook for anyone who wants to try and solve the problem. Greatly appreciate the help.PowerBI Date LogicUse a date slicer to dynamically filter last know value for a given listing
Hi, I am currently struggling trying to set up a HR positions listing. My entry data is the HR listing displaying for each employee, all their positions held and for each position held their starting date (and ending date if applies) : EmployeeID LASTNAME FIRSTNAME STARTING DATE POSITION ENDING DATE POSITION SITE DPT NAME POSITION NAME POSITION LEVEL 1 SMITH Paul 01/01/2019 28/02/2022 PARIS CallCenter Operator 4 1 SMITH Paul 01/03/2022 PARIS CallCenter Supervisor 6 2 ADAM John 01/01/2019 28/02/2019 LYON Telecom Center Junior Assistant 3 2 ADAM John 01/03/2019 28/03/2019 LYON Telecom Center Assistant 4 2 ADAM John 29/03/2019 31/03/2019 LYON Telecom Center Senior Assistant 5 2 ADAM John 01/04/2019 31/05/2023 PARIS Telecom Center Manager 7 5 CLARK Kent 01/01/2019 28/02/2022 BREST Financial Direction Accountant 6 10 PARKER Lisa 01/01/2019 30/09/2020 TOULOUSE HR Department Secretary 5 10 PARKER Lisa 01/10/2020 28/02/2022 PARIS CallCenter Supervisor 6 10 PARKER Lisa 01/03/2022 28/02/2023 TOULOUSE HR Department Manager 7 10 PARKER Lisa 01/03/2023 TOULOUSE HR Department Director 8 The end goal is to have the last position held by all employees at a given date. I managed to do the following : Add a DAX Column in my HRListing table that gives me for each entry the last starting position date LastPositionDate = var currentID = 'HRListing'[EmployeeID] var result = CALCULATE ( LASTDATE( 'HRListing'[STARTING DATE POSITION]), ALL(), 'HRListing'[EmployeeID] = currentID ) return result Create a DAX table filtering on that last position date HRListingLastPosition = FILTER ( 'HRListing', 'HRListing'[STARTING DATE POSITION] = 'HRListing'[LastPositionDate] ) This is actually working as it is giving my the last uptodate HRListing with the last position held by employees : EmployeeID LASTNAME FIRSTNAME STARTING DATE POSITION ENDING DATE POSITION SITE DPT NAME POSITION NAME POSITION LEVEL 1 SMITH Paul 01/03/2022 PARIS CallCenter Supervisor 6 2 ADAM John 01/04/2019 31/05/2023 PARIS Telecom Center Manager 7 5 CLARK Kent 01/01/2019 28/02/2022 BREST Financial Direction Accountant 6 10 PARKER Lisa 01/03/2023 TOULOUSE HR Department Director 8 But what I am actually trying to do is to incorporate a date slicer in my visual where I can set a date and get the last uptodate HR listing for that current date. For exemple : This specific slicer would give me the "screenshot" of the last held position of all employees for this specific date. What I want with such a slicer is this result : EmployeeID LASTNAME FIRSTNAME STARTING DATE POSITION ENDING DATE POSITION SITE DPT NAME POSITION NAME POSITION LEVEL 1 SMITH Paul 01/01/2019 28/02/2022 PARIS CallCenter Operator 4 2 ADAM John 01/04/2019 31/05/2023 PARIS Telecom Center Manager 7 5 CLARK Kent 01/01/2019 28/02/2022 BREST Financial Direction Accountant 6 10 PARKER Lisa 01/10/2020 28/02/2022 PARIS CallCenter Supervisor 6 I tried playing with a slicer with both dates from the HRListing table and the DAX created HRListingLastPosition table but this is not giving me the expected result Any ideas on how to achieve such a thing ? Thank you very muchFilter 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.Dynamically Add Levels to Hierarchy Slicer
Hi All - We have two tables i.e Pipelines and Activites, Pipeline table includes PipelineId, ParentPipelineId and PipelineName etc and Activites includes activity info related to Pipeline. We want to create Levels Dynamically from Pipelines Table and Use the Levels to display in Hierarchical Slicer. I was able to get levels dynamically by using Power Query Function i.e Dynamically flatten Parent-Child Hierarchies in DAX and PowerBI – (thebiccountant.com) but unable to create slicer dynamically i.e currenlty one of the Pipeline has 4 levels and if we change the pipelineId to different one it might have 7 levels. Can you please suggest how can we achive this functionality? Thanks, Abhiram
Filtering a slicer's values based on the values of another table
Hi everybody! My goal is to filter the values of a slicer, based on the values selected from another table. Problem description: I have a slicer which shows the values of a dynamic parameter (named Y1-Axis Params) as the following: The dynamic parameter (named Y1-Axis Params) is defined as the following. So, in the table view, you should see three columns as the following: And, I have another table (named: Table_Params_List) which contains the Id and name of some parameters, as the following: I want to filter the slicer’s values based on the selected values from this table, so: 1- first I defined a measure to return the Id of the selected value: Selected_ParamId = SELECTEDVALUE(Table_Params_List[Id],0) 2- then, I used a custom column to save the Id of all selected values: Selected_ParamIDs = var _selectedIDs= [Selected_ParamId] return _selectedIDs So far so good. Now Selected_ParamIDs shows the IDs of all selected rows from Table_Params_List: Now, I want to filter the slicer’s values based on the values of this custom column. For this purpose: I defined a new measure (named: Include?) as the following: Include? = var _selectedIDs= VALUES(Table_Params_List[Selected_ParamIDs]) return IF( MAX('Y1-Axis Params'[Y=Axis Params Order]) in _selectedIDs, 1, 0) And then, I added this measure (using dag and drop) to the Filters pan of the slicer visual and configed it as the following: As you can see, in this way, I could get the IDs of the selected items from the table, but the slicer still shows all of the values! ** But, if I use a measure as the following, it works well and filters the values of the slicer!! Include? = IF( MAX('Y1-Axis Params'[Y=Axis Params Order]) >= 2 , 1, 0) So why it doesn’t work when I try to use the variable _selectedIDs? (But it works if I use some fixed values for the comparison!) Any advice is very appreciated! Thanks and best regards! Farhad
