Replacing blanks with previous count in continuous line chart
Hi All I'm hoping someone will be able to help me modify this measure. I've tried a number of ways to make it work but can't quite seem to get it right. Where there is a blank week (Wk 9 in this example, I would like the figure from the previous week, e.g. 100 (Wk 8), so that my line is continuous and doesn't drop to 0. My measure so far is: 2024_cate_tickets = COUNTROWS ( FILTER ( ALL ( 'cate' ), 'cate'[week_num] <= MAX ( 'cate'[week_num] ) && 'cate'[date_registered] <= MAX ( 'cate'[date_registered] ) && 'cate'[event] = "cate 2024" ) )
Correct Total using ALL() whilst also having blanks filtered out of visual?
Hi I'm hoping someone may have a quick solution to a problem i'm encountering whilst trying to provide % of total in a bar chart. The screenshot below shows a dummy dataset for customer feedback replicating the issue i'm encountering. The data spans over 3 days (20th Oct to 22 Oct), with a total of 25 customers, 9 of which opted not to give feedback. I'm needing to present this data as a % broken out by feedback score. I have used the 'feedback%' measure shown below, which leverages the ALL() function to clear the feedback_rating filter, providing a total customer count. You can see from the 2nd bar chart that this works when the blank feedback rating option is included in the visual. Example feedback score of 1 has 5 customer entries, 5/25 = 20%. I have been asked to remove blanks from the visual. When these are removed however, the % are incorrect as can be seen within the red bar chart, a feedback score of 1 now has a % distribution of 26%. The reason appears to be due to the Date slicer which is required in my real dashboard. As all the customers on 21st October did not leave feedback, I believe when the blanks are removed from the visual, all of these rows are removed from the underlying data, creating a new customer total of 19, resulting in incorrect %'s, feedback score of 1 now becoming 26% (5/19) as mentioned above. Any ideas on how to keep these values in the total whilst also keeping the blank entries out of the visual? Ideally I would like to solve it within a DAX measure as my existing dashboard already has a relatively complex data model, however I appreciate it may require a helper table of some sort. feedback% = distinctcount(Sheet1[customer_id]) / CALCULATE(DISTINCTCOUNT(Sheet1[customer_id]),ALL(Sheet1[feedback_rating])) Thanks for reading.
Remove blank childs in a Gantt chart with parent-child hierarchy
I cannot figure out a way to set up a Gantt chart that shows the full hierarchy (project - lv1, story - lv2, task - lv3), without showing blank childs (or default childs, parent as child). In my hierarchy, some stories (level 2) may not have child tasks, therefore the column "Task - LV3", calculated using PATHITEM, is empty. I am using the Gantt Chart by MAQ Software. I have created a filter measure that, if placed in a matrix with the same dimensions, only shows childs if a parent have some: Remove Blanks = SWITCH(TRUE(),AND(ISINSCOPE(Table[Level2]), ISBLANK(VALUES(Table[Level2]))), BLANK(), AND(ISINSCOPE(Table[Level3]), ISBLANK(VALUES(Table[Level3]))), BLANK(),1) However, I cannot find a way to make it work in the Gantt Chart. If I try to use it as a filter, it will only shows full hierarchies, namely items that have lv1 & lv2 & lv3, therefore losing all the stories (lv2) and projects (lv1) that do not have tasks (lv3). Can someone help? ThanksRunning Total with blank value not cumulating values
Hello, I have build up this formula to retrieve Running Totals for the amount of hotel chains signed in the past years: Running Total Hotel Chains Signed = CALCULATE( DISTINCTCOUNT(tbHotelChains[IdHotelChain]), FILTER( ALL(tbHotelChains), tbHotelChains[CommercialNotificationDate]<= MAX(tbHotelChains[CommercialNotificationDate]) ) ) However the results I get is the following: Year Month Count of IdHotelChain Running Total Hotel Chains Signed 2022 January 1 1933 2022 February 1 1934 2022 March 1872 2022 April 1872 2022 May 4 1938 2022 June 4 1942 2022 July 1 1943 2022 August 1 1944 2022 September 1872 2022 October 1872 2022 November 1872 2022 December 1872 2021 January 3 1907 2021 February 6 1913 2021 March 4 1917 2021 April 4 1921 So that when data is BLANK, the running total is getting bacl to previous values, how can I avoid that? ThanksConditional Formatting of Blank Values in Matrix using DAX
Hello, I have a matrix with working times of users, where rows represent invidividual users and columns are dates. Cells represent users working time in a specific day. I want to highlight all cells with value less than 8 and all blank cells with red colour. I need to do this by creating a measure using DAX and not the rules available in conditional formatting. Example data look like this: UserTable: DateTable: HoursTable holding the actual data: In the model, the UserTable.id is connected to HoursTable.userID and DateTable.Date is connected to HoursTable.Date. It looks like this: In the HoursTable, I've created a measure Colouring that determines the colour of each cell in a matrix. If the value in cell is less then 8, assign red colour. If it is black, assign red colour. Otherwise, assign no colour. I've created a Matrix visual using my data And assigned conditional background colour formatting based on the measure value As a result, I got a Matrix where cells with value less than 8 have red background. However, the blank values have no colour: However, if I add the Colouring measure to the matrix as an additional value to be displayed Then the background of blank cells is suddenly coloured in red Naturally, this is not desirable because the Colouring measure should not be part of the Matrix values What am I missing here? Why is it behaving like this and the background is coloured properly only when I add the Colouring measure to the Matrix visual? How should I create a DAX measure or modify my data model to achieve what I desire?Help needed. How to calculate %. Denominator is being changed by a slicer?
I am having trouble with the denominator in a division calculation. The denominator is being changed when items in a slicer are selected. I have used REMOVE FILTERS and ALL functions in the measure calculating the denominator, however the denominator value still changes when individual items in the slicer are selected. The only exception is when the "Blank" item in the slicer is selected combined with another slicer option (see below). How do I stop the slicer from changing this measure. ("Edit Interactions" option is not appropriate). I am calculating the number of Total Tasks with Errors and slicing by the "Error Reason" . "Blank" in the "Error Reason" column represents Tasks that have no errors. "Tasks with Errors " is an number column & "Error Reason" is a text column. Your help is appreciated. Measure used: Reason % = Var Numerator = CALCULATE(SUM('Worksheet'[TasksWithErrors])) Var Denominator = CALCULATE(SUM('Worksheet'[TasksWithErrors])), REMOVEFILTERS('Worksheet'[ErrorReason])) RETURN DIVIDE(Numerator,Denomoinator,0) Visuals Visuals when Slicer selected What happens when the "Blanks" are selected in the slicerKEEP TOTAL NO DATA RELATED
Hello, Anyone could help me? I have a measure that brings me values based on another column of the same table (A). When I add a field from another table (B) it shows only the values that has data of this another table (B). How do I keep the values of table (A) even if they do not have any relation with data of table (B)? Following the screens with the DAX code and tables with its totals. The totals should be the same in 1 and 2. I’ve already tried to apply in the DAX code ALL(), REMOVEFILTERS(), RELATED(), but didn’t work. Regards,
