Display records from Table B if and only if a single row of Table A is selected
I have two tables, one is a summary of values and the other is detailed values by date and sub-category. They are connected by an id-date key column. When I have a single table and want to display it at two different detail levels I can use a measure that returns true if a single value is selected or false if no single value is selected and use that as a filter on the detailed view so that it doesn't show anything unless a single record is selected in the summary view. When I try this same technique for my current scenario, where I have two separate tables, Table B always displays records regardless of if a single value in Table A is selected or not. See here, no row is selected in Table A on the left, so no records should display on the right, this screenshot is the bad behavior: Desired behavior here where when no row is selected in Table A, nothing from Table B is displayed: But when a record in Table A *is* selected, data from Table B is then displayed: I've spent a whole mess of time on this and no combination of managing/removing filters or changing relationships used in my DAX query seems to work. I feel like I'm missing something obvious as to why this isn't working for me.
Visualising Two Non Numeric Columns
Hello , I have three fields Date,Name and Percentage.To show it in a visual. I want to group [Names] with [Percentage] column or [Date] with [Percentage] with the help of DAX . Since there are so many items in Names column and also there are two non numeric columns. That way I dont have to use Names or Date column in the visual as two Non numeric columns cant be used. I dont want to use Legends and Slicers bcoz there are too many Items in Name columns. It would be difficult for analysis. The Left Side side I need in visual.Please help. Please tell if I am thinking correctly.create dax measure to remove context filter
hi everyone im trying to create a measure that remove the conext filter in a table visual with if statement condition i was trying to use 'all' function to remove the filter from the column but still not working. current measure : calculate(sum('categories trans'[achievement]), all('categories trans'[Base_type]) in the above example , i want to ignore the filter by (base_type) column in one category (zeed) and keep it for the other categories. for example: if category= 'zeed' i want the achievement measure to return 441 and for other categories i want the achievment measure to return same values above.Return Max Value of Category when Data has Two Different Demands With and WIthout a Subcategory
Hello, I’m trying to list two different demands for each Category in a table, one of which has the demand further broken down by Type but where this isn't the case for the other, which just has the aggregated value for Category. The Category also needs to be mapped to its particicular month - there are repeating Category names populating different months in the year - and work with further filter contexts in the table. For the demand without the breakdown by Type, I understand I need to return the max value per Category rather than having all the values summed. An example of the data is shown below: My data repeats across the year for different Categories but as seen, is only broken down by Type for Current Demand. When I list things in a table, I’m looking for a way to list both demands to compare but importantly, also a way to list the demand by Type for Current Demand (or ideally any other field whether at a more aggregated or broken down level) even though we don’t have this level for Previous Demand. Whether Previous Demand just shows no entries or has fake placer values to reach the correct aggregate I’m not much fussed about. So far I have tried creating the following measures but with no success: Previous Demand Max = MAX(Products[Previous Demand]) Previous Demand = CALCULATE([Previous Demand Max], ALLEXCEPT(Products, Products[Category])) Any ideas on how I could acheive this would be much appreciated.
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
