Filter a table based on multiple selected values
Hi all, I am currently using a switch() statement within a measure to filter a table appropriately depending on a selection like so: _Orders_BY_superhero_preference = VAR SelectedOption = SELECTEDVALUE(SuperHeroes[name]) VAR code = SELECTEDVALUE(SuperHeroes[code]) VAR all_codes = SELECTCOLUMNS(SuperHeroes,"code",[code]) RETURN CALCULATE([_Orders],FILTER(OrderTable, OrderTable[matched] = "Y" && OrderTable[order_number] IN all_codes && SWITCH( SelectedOption, "Batman", RELATED('CustomerAttributes'[Fav_superHero]) = "Batman" && CONTAINSSTRING(OrderTable[order_number],code), "Spiderman", RELATED('CustomerAttributes'[Fav_superHero]) = "Spiderman" && CONTAINSSTRING(OrderTable[order_number],code), "Superman", RELATED('CustomerAttributes'[Fav_superHero]) = "Superman" && CONTAINSSTRING(OrderTable[order_number],code) ))) Where the measure [_Orders] is basically a DISTINCTCOUNT of order ids, and the rows included in the aggregation are determined by which superhero name is selected. This works with a single option eg: name = Batman. However, is there a way that I can modify this so that it works with multiple selected options? In other words if Both Batman and Spiderman are selected, the OrderTable is filtered such that it contains rows where the 'matched' column = "Y", the order_number value exists in the SuperHeroes code table, and only rows where the related values in the customerAttributes table are Batman or Spiderman are included in the aggregation? I've tried replacing SELECTEDVALUE with VALUES, but having some trouble with figuring out how to account for multiple selections within the switch logic.Why do my SWITCH measure results have a long number despite selecting 2 decimals?
To give a bit more info, this is the main part of how I insert the values into the SWITCH measure. Please note it's a very shortened version, but includes the two main types of values (with and without additional text) var delta_etc_pfe_perc = IF( LEN([Δ ETC vs PFE %]) > 0, [Δ ETC vs PFE %], 0 ) var result = SWITCH( TRUE(), /* Metric = Days */ metrics = 1 && selected_column_1 = "Planned", [Frc Days], metrics = 1 && selected_column_1 = "Δ ETC vs PFE", [Δ ETC vs PFE] & " (" & delta_etc_pfe_perc & ")", So in this case, my value Delta ETC vs PFE is numerical, but the variable I insert after is text because SWITCH doesn't allow me to insert numerical percentage variables, as they will become reformatted as non percentage. So I format as text the measure as I calculate it on its own as follows: FORMAT(Result, "0.00%") and then here make that if() call on it As you can see I also added a measure to conditionally colour only some of the values, and I just want to state beforehand that changing that won't affect the result. Why do my SWITCH measure results look like this? I already fixed the decimal number to 2 values. It only happens for those rows where I stated that it should give me both the result of an operation and then another one within parenthesis. But both of those values I also fixed to 2 decimals only
Calculated measure for conditional formatting returns inconsistent values.
Hi Experts, I have a typlical scenario wherein the caluclated measure created for conditional formatting is behaving inconsistently. Measure is defined as: Color subtotal = var _laying = CALCULATE([Total SES Qty],FILTER(All(Activity), Activity[Activity_ID] = "01L")) var _main_sor = CALCULATE([Total SES Qty],FILTER(All(Activity), Activity[Activity_ID] IN {"01L", "02O", "03V", "04R"})) var _commissioning = CALCULATE([Total SES Qty],FILTER(All(Activity),Activity[Activity_ID]="05C")) var _documentation = CALCULATE([Total SES Qty],FILTER(All(Activity),Activity[Activity_ID]="06D")) var _safety = CALCULATE([Total SES Qty],FILTER(All(Activity),Activity[Activity_ID]="07S")) var _barricading = CALCULATE([Total SES Qty],FILTER(All(Activity),Activity[Activity_ID]="08B")) var _selection = SELECTEDVALUE(Activity[Activity_ID]) return SWITCH(TRUE(), AND( (_selection = "05C"), (_commissioning <= _main_sor) ), 1, AND( (_selection = "06D"), (_documentation <= _main_sor) ), 1, AND( (_selection = "07S"), (_safety <= _laying) ), 1, AND( (_selection = "08B"), (_barricading <= 2*_laying) ), 1, _selection IN {"01L", "02O", "03V", "04R"}, BLANK(), 2) Expected result : If quantity for safety is <= that for laying, then measure returns the value 1 and background color should be green. It should be red otherwise. The measure is reutning value 2 for the first row in the below screen shot (marked with arrow) and hence color is red. However it should be green. However the result is correct for the second row formatted in green color. The details (data) for both the rows is displayed below the matrix. Data for the problem scenario is circled in red. Settings for conditional formatting are as under: I checked the values returned by the measure. It is returning 2 for the problem scenario. I incresed the decimal places to 4 to identify rounding off issues, but that is not the case. Please help me resolve this typical behaviour. SolomonovAnton - You had helped me with setting up this conditional formatting. You might understand this better. Please help.
