Date time comparison with NOW() function
Hello Everyone, I have an issue with comparing two datetime values. The goal is to mark all the rows of data that are within 6:00:00 AM 3 days ago and 6:00:00 AM today: As you may see upper expression returns NO for datetime that is within last 3 days and i do not understand why is that. Two clues: 1. When i am doing IF statement only for IF CurrentRowDate>=Is3Day it returns only datetime rows for past year. Condistion is not meet for current year. 2. Previously report was build for periods midnight to midnight, i wasn't including time part - it worked. Can someone please explain what is goind on? DateTime column is Date/Time format but maybe it is still the case of different formatting used in expression?Filtering a Multi-Select Slicer via AND instead of OR logic
In Power BI, slicer visuals work by restricting the rows of data accessible by visuals in the report. Multiselecting slicers unions the criteria by giving rows for all criteria. What is not possible with the default way Power BI filters things is to multiselect data with an intersection (using AND instead of OR logic) to only give rows that fit BOTH the selected criteria. For the report I am working on, I was asked to configure a way so that multi-selecting of our filter categories would filter data via this AND intersection opposed to the Power BI default. Other Solutions: Upon initial research (read: extensive googling!) I came across the following four articles which detail different approaches to achieving this. They all generally tackle the issue in a similar way – using measures to compare counts of selected items versus the count of rows matching these selected criteria. Slicer with AND condition in Power BI - RADACAD Apply AND Logic to Multiple Selection in DAX Slicer - SQLBI Changing “OR” to “AND” Logic for Power BI Slicers | by Orysya Stus | Seismic Innovation Labs | Medium Power BI: Implement AND/OR Selection | by ZhongTr0n | Towards Data Science However, due to the structure of the data I was working with and the specific needs of the report, none of these solutions exactly worked. The Data: For the sake of privacy I cannot share the actual data of the report although for developing the right method to replicate this ‘AND’ filter I can show a snippet of the structure of the data I am working with. Requirements: The specific solution I was tasked to create was a way to multi-select a slicer based on [Course] to filter visuals that all concerned the number of distinct Employees (based on unique key field [Email]). Without any of the AND logic implemented, the report looked like the following: You can see we have a count of the number of trainees broken down by different categories (hidden for the sake of privacy). When different courses are selected the data shown dynamically changes to include rows relating to either Course 1 OR Course 2 OR Course 3… and so on. Solution: To change this logic to instead give rows of one table (Employee Head Count) relating to multiple rows in another one (Course 1 AND Course 2 AND Course 3 …): DAX Measures! I have broken down my solution into two different parts corresponding to the two measures I have made. Pt 1. Trainees Count Without any AND logic, our original measure to count the number of employees who had completed training was the following: Trainees = DISTINCTCOUNT('Training Records'[Email]) To implement AND logic, we result in the following: numTraineesCompletedALLSelected = //the number of trainees who have selected all completed courses // count the number of criteria (courses) selected var selectedCourses = VALUES(Course[Course]) var numSelectedCourses = COUNTROWS(selectedCourses) //count number of selected courses completed per trainee var result = COUNTROWS( FILTER( GROUPBY( FILTER('Training Records', RELATED('Course'[Course]) in selectedCourses), 'Employee Head Count'[Email], "SelectedCoursesCompleted", COUNTX(CURRENTGROUP(), [Course]) ), [SelectedCoursesCompleted] >= numSelectedCourses) ) //if courses are being filtered: filter with AND condition //else count trainees normally return IF(ISFILTERED(Course[Course]), result, DISTINCTCOUNT('Training Records'[Email]) ) Let’s break this down, To start off we create two variables selectedCourses and numSelectedCourses which extract the courses we have selected via the slicer. Then we have the result variable – the main chunk of the logic. If we focus into the middle bit we have a GROUPBY function wrapping COUNTX with a filtered table. What this does is, from our training records table filtered to only have rows corresponding to the selected criteria, we count the number of courses (as per the COUNTX) grouped by each employee email. This gives us a table for each employee with a corresponding count of how many of the selected courses they have completed. We then further filter this resulting table to only give us employees who have completed at least the same number as the amount of selected courses. The final function is to COUNTROWS of this table to provide a number of how many employees have completed ALL of the selected courses. The final return part of the measure has an extra logic check to see if our criteria is being filtered at all. If it is, we return the result just calculated, if not, we default back to the usual way to count the number of employees. This new measure numtraineesCompeltedALLSelected (pending a name improvement lol) is what we use to replace the original Trainees measure. Pt 2. Count Courses Per Trainee While the previous measure sorts us out for visuals directly using the count of trainees (eg. Our bar graph, or card), what this does not account for is our table visualisations which have data rows corresponding to individual trainees from the Employee Head Count table. To solve this issue, I created a second measure, coursesCompletedPerTrainee = // count the number of criteria (courses) selected var selectedCourses = VALUES(Course[Course]) var numSelectedCourses = COUNTROWS(selectedCourses) // count the number of courses per trainee var coursesPerTrainee = COUNTX( FILTER('Training Records', RELATED(Course[Course]) in selectedCourses), 'Training Records'[Email]) var ANDCheck = IF(coursesPerTrainee >= numSelectedCourses, 1) //return non-blank if courses are not being filtered return IF(ISFILTERED(Course[Course]), ANDCheck, "Non-Blank") Looking very similar to our previous measure, our main difference is that this measure no longer uses a GROUPBY function. Instead, our return value is the number of courses (of the selected courses) completed per trainee (calculated with the COUNTX function). This is then used to filter our table by If we are not filtering at all we simply return a string “Non-Blank” which avoids errors when no criteria is selected. Summary When attempting this solution I found it necessary to have two measures to achieve two main things: 1) A total count of trainees who had completed selected courses (pt. 1) and 2) a measure by which to filter table visuals to only return relevant employee records (pt. 2). This is the main difference I found between my solution and the four previous articles I read in research. I found the difference in how the data I was using was structured compared to the sample data in solutions brought about this requirement as I was concerned with counting the records in one table (Training Records) grouped by the rows of another (Employee Head Count). I would be very interested in hearing of a way to optimize this and perhaps result in only one measure which can be used for the two purposes – the similarity in their code makes me believe this may be possible. For the time being however, I have developed this as the solution to meet the needs of the project as it results in the relevant output being provided for the report.
Search In Different Order
I'm looking to have a search field that looks in a table where it searches the same words, but the words could be in a different order. In excel, it would be similar to the contains and contains filter instead of the exact filter. Example Description Field: Pen Blue Medium Bic In the search field if "blue pen" is entered I would like for this description to come up. It's only coming up if I type the words in the exact same order. Eg. "Pen blue", "blue medium" & "medium bic" Thanks in advance for your help! 🙂 Laurinda
Advanced filter experience: "AND", "OR" , "NOT" filter logic using slicer visuals and DAX!!
Overview There are a number of examples that have shown how to add logic so that you can use filters with an "AND" operator rather than the standard "OR" logic when using a slicer. This example shows how to also add the ability for a "NOT" statement as well, to create what I think is an incredibly powerful level of flexibility for end users. The attached example has been built using open data and shows how the approach works Download PBIX example Limitations - slow performance with large datasets I am looking for help and suggestions on how to improve performance of these measures using DAX Studio or by re-engineering the approach. In my actual report the tables have millions of rows and performance is slow, the current approach results in a very large number of engine queries and depending on capacity in Power BI server can even result in visuals timing out due to lack of memory. How it works (be warned, it may get confusing...) I won't go over the full extent of how to implement AND filter logic. The approach I have used is based on the following excellent blog post. Power BI: Implement AND/OR Selection | by ZhongTr0n | Towards Data Science In my example, I have extended the logic to also allow users to EXCLUDE items. The core logic is the same, and the EXCLUSION criteria can be applied using either an OR logic or AND logic operator (i.e. Filter for Items that DON'T have X OR Y attribute versus Items that DONT have X AND Y attribute) 1. If the attribute slicer is filtered, the measure below either counts the number of distinct items in the attribute list (if using an AND logic) or returns a 1 if using the OR logic AndOrLogicSwitch (NOT) = IF ( ISFILTERED ( 'LSOA Attributes - Filter (NOT)'[Attribute] ), IF ( SUM ( 'ANDOR_Table (NOT)'[Binary] ) = 0, CALCULATE ( DISTINCTCOUNT ( 'LSOA Attributes - Filter (NOT)'[Attribute] ), ALL ( 'LSOA Master') ), 1 ), CALCULATE ( DISTINCTCOUNT ( 'LSOA Attributes - Filter (NOT)'[Attribute] ), ALL ( 'LSOA Master') ) ) 2. A further measure then compares, for each item in the master table, the number of attributes the item has compared to the measure value. If the item has fewer attributes than the number of attributes in the above measure, then it does NOT meet the exclusion criteria and is therefore included. This is also combined with similar logic for the INCLUSION criteria, which is essentially the same logic but in reverse. ANDORNOT Logic = VAR ANDORCondition = [AndOrLogicSwitch] VAR NOTCondition = [AndOrLogicSwitch (NOT)] VAR Include = and( // This logic determines if the item meets the INCLUDE criteria or(not(isfiltered('LSOA Attributes - Filter'[Attribute])),DISTINCTCOUNT ( 'LSOA Attributes - Filter'[Attribute]) >= ANDORCondition) // This logic determines if the item meets the EXCLUDE criteria , or(not(isfiltered('LSOA Attributes - Filter (NOT)'[Attribute])),DISTINCTCOUNT ( 'LSOA Attributes - Filter (NOT)'[Attribute] ) < NOTCondition)) RETURN Include 3. Further measures can then be calculated, using this logic to filter the master table, for example Count of LSOAs = var LSOACount = countrows(filter('LSOA Master',[ANDORNOT Logic])) return LSOACount Hopefully this will be helpful to others, and I would be very grateful for any ideas on how to improve on what I have done, particularly to improve performance for larger datasets. Thank you AlexAdvanced filter experience: "AND", "OR" , "NOT" filter logic using slicer visuals and DAX!!
Overview There are a number of examples that have shown how to add logic so that you can use filters with an "AND" operator rather than the standard "OR" logic when using a slicer. This example shows how to also add the ability for a "NOT" statement as well, to create what I think is an incredibly powerful level of flexibility for end users. The attached example has been built using open data and shows how the approach works Download PBIX example Limitations - slow performance with large datasets I am looking for help and suggestions on how to improve performance of these measures using DAX Studio or by re-engineering the approach. In my actual report the tables have millions of rows and performance is slow, the current approach results in a very large number of engine queries and depending on capacity in Power BI server can even result in visuals timing out due to lack of memory. How it works (be warned, it may get confusing...) I won't go over the full extent of how to implement AND filter logic. The approach I have used is based on the following excellent blog post. Power BI: Implement AND/OR Selection | by ZhongTr0n | Towards Data Science In my example, I have extended the logic to also allow users to EXCLUDE items. The core logic is the same, and the EXCLUSION criteria can be applied using either an OR logic or AND logic operator (i.e. Filter for Items that DON'T have X OR Y attribute versus Items that DONT have X AND Y attribute) 1. If the attribute slicer is filtered, the measure below either counts the number of distinct items in the attribute list (if using an AND logic) or returns a 1 if using the OR logic AndOrLogicSwitch (NOT) = IF ( ISFILTERED ( 'LSOA Attributes - Filter (NOT)'[Attribute] ), IF ( SUM ( 'ANDOR_Table (NOT)'[Binary] ) = 0, CALCULATE ( DISTINCTCOUNT ( 'LSOA Attributes - Filter (NOT)'[Attribute] ), ALL ( 'LSOA Master') ), 1 ), CALCULATE ( DISTINCTCOUNT ( 'LSOA Attributes - Filter (NOT)'[Attribute] ), ALL ( 'LSOA Master') ) ) 2. A further measure then compares, for each item in the master table, the number of attributes the item has compared to the measure value. If the item has fewer attributes than the number of attributes in the above measure, then it does NOT meet the exclusion criteria and is therefore included. This is also combined with similar logic for the INCLUSION criteria, which is essentially the same logic but in reverse. ANDORNOT Logic = VAR ANDORCondition = [AndOrLogicSwitch] VAR NOTCondition = [AndOrLogicSwitch (NOT)] VAR Include = and( // This logic determines if the item meets the INCLUDE criteria or(not(isfiltered('LSOA Attributes - Filter'[Attribute])),DISTINCTCOUNT ( 'LSOA Attributes - Filter'[Attribute]) >= ANDORCondition) // This logic determines if the item meets the EXCLUDE criteria , or(not(isfiltered('LSOA Attributes - Filter (NOT)'[Attribute])),DISTINCTCOUNT ( 'LSOA Attributes - Filter (NOT)'[Attribute] ) < NOTCondition)) RETURN Include 3. Further measures can then be calculated, using this logic to filter the master table, for example Count of LSOAs = var LSOACount = countrows(filter('LSOA Master',[ANDORNOT Logic])) return LSOACount Hopefully this will be helpful to others, and I would be very grateful for any ideas on how to improve on what I have done, particularly to improve performance for larger datasets. Thank you Alex
