Slicer Default Setting and configurations
Hello Experts, Please can you share the DAX commands or other ways where I can set my filters to default value. Requirement: Default last 3 year financial years have to be displayed from current date. I have a slicer with Hierarchy and able to display Year, Quarter and month 2. I have to set default to all the filters I have .. 3. I have attached a picture, when I sort the Financial Year are sorting not just the quarters in this. Any help
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
