What is wrong with my DAX formula? Using INSCOPE for % of parent and child categories
Hello, In my screenshot below, you can see that I have a 100% stacked bar chart. The first level is great and shows how the students scored (Exemplary, Proficient, Not Provided) on each of the 3 tests. However, when I go to drill down to the next level, which is gender, I am getting 2 different percentages and one is correct while the other is not. I have also included my DAX below. I would appreciate if someone could tell me where I am going wrong. Thanks! I'm not sure if this is important to note, but in the above bar charts, the "sex" variable is coming from a separate (but related) table which is Table2. I have a =RELATED column in Table1 with the Sex information (as you can see in my DAX below) but when I put that column into the stacked bar chart, the percentages are even more incorrect. Therefore, the closest to correct that I can get is by placing the "Sex" column from Table2 into the bar chart and the "Sex" column from Table1 into the DAX measure. Thanks! My DAX: % = VAR CurrentCount = CALCULATE(DISTINCTCOUNT('Table1'[StudentID])) VAR ParentTotal = SWITCH( TRUE(), ISINSCOPE('Table1'[Sex]), CALCULATE(DISTINCTCOUNT('Table1'[StudentID]), ALLSELECTED('Table1'[Sex])), CALCULATE(DISTINCTCOUNT('Table1'[StudentID]), ALLSELECTED('Table1')) ) RETURN DIVIDE(CurrentCount, ParentTotal)
All Selected is not working properly, I have to include another Column
Hello, I have a fact table that has multiple colums: Item,Period_Name,Period_ID,Sales Period name is like Jan 2022 , Feb 2022 etc Period_ID is like 202201 , 202202 etc I created one slicer in the dashboard that has "Period_Name" Field and created 1 grid in the dashboard in which I want to include 4 columns: Item, Period, Sales Value, Sales value (for the all selected periods) , Sales Value (for all the periods selected and unselected) I created the measures like this Total Value Sales = CALCULATE(SUM(Fact_Sales[Value Sales])) Total Value Sales (Selected Period) = CALCULATE([Total Value Sales], ALLSELECTED(Fact_Sales[Period])) Total Value Sales (Ignore Period) = CALCULATE([Total Value Sales], ALL(Fact_Sales[Period])) But I got wrong results like the image I tried so much to solve it and solved successfully by changing in the measures Total Value Sales (Selected Period) = CALCULATE([Total Value Sales], ALLSELECTED(Fact_Sales[Period]),ALLSELECTED(Fact_Sales[PERIOD_ID])) Total Value Sales (Ignore Period) = CALCULATE([Total Value Sales], ALL(Fact_Sales[Period]),ALL(Fact_Sales[PERIOD_ID])) As you can see, numbers now are reflecting correctly... but I am unable to understand the behaviour, why it didn't work although I am not using this Period_ID column anywhere? also I wanted to create another sheet with dummy data to upload here to the community to make things easier, but surprisingly it worked successfully without adding the second condition. Does anyone have a clue? Thanks. John Elmasry
Writing measure to show multiple visuals on the same chart with one slicer
Hello, I am sorry to keep posting looking for a solution on this. I am trying to write a measure. I have tried multiple methods but cant get anything to work. I would assume having two different measures on the same visual would work. I have two tables (Table1 and Table2). My goal is to use one slicer and show multiple visuals from the same table on the same chart. For example, if a month is selected, I want to show data (filtered with different conditions) for that month, and the month most recent to the selected month. Column 'Table1'[Month] is linked to Column 'Table2'[Month] with a one to many relationship (Table2-one, Table1-many). Then I have a slicer that uses column 'Table2'[Month]. The visual for my current month works, but now I am trying the add the data to the same visual for its previous month. For the previous month, If the user selects a month with the slicer, I want to save the [Previous Month] associated in Table2 column as text. So if 24-Jun is selected, I want to save "24-May" as text. Then I want to filter and count the number of rows in Table1 where [Month End] = the saved text & Type = "Task". So in this example, I would count the number of rows in Table1 where Table1[Month End] = "24-May" & Type = "Task", and this would return 1. Table1 ID Status Date Rank Previous Status Month Type Group Previous Month A Normal 3 No previous 24-Mar Task <0 No previous B Normal 3 No previous 24-Mar Task 1 to 5 No previous C Not Normal 3 No previous 24-Mar Task 1 to 5 No previous D Not Normal 3 No Previous 24-Mar Not Task 11 to 15 No previous A Not Normal 2 Normal 24-May Task 6 to 10 24-Mar B Normal 2 Normal 24-May Not Task 6 to 10 24-Mar C Normal 2 Not Normal 24-May Not Task 6 to 10 24-Mar D Not Normal 2 Not Normal 24-May Task 6 to 10 24-Mar A Not Normal 1 Not Normal 24-Jun Task 1 to 5 24-May B Not Normal 1 Normal 24-Jun Task <0 24-May D Normal 1 Normal 24-Jun Task <0 24-May E Normal 1 24-Jun Task 1 to 5 24-May Table2 Date Rank Month Next Date Rank Previous Month 3 24-Mar 4 2 24-May 3 24-Mar 1 24-Jun 2 24-May Sample of visual I'm trying to create: Measure that doesn't work for previous month: (I keep getting blank when selecting a current month) VAR SelectedMonth = SELECTEDVALUE('Table2'[Month]) VAR PreviousMonth1 = CALCULATE( MAX('Table2'[Previous Month]), FILTER( ALL('Table2'), 'Table2'[Month] = SelectedMonth ) ) VAR CountRows1 = CALCULATETABLE( 'Table1', FILTER( ALLSELECTED('Table1'), 'Table1'[Month] = PreviousMonth1 && 'Table1'[Type] = "Task" ) ) RETURN COUNTROWS(CountRows1) Measure works for selected month: VAR SelectedMonth = CALCULATE( MAX('Table1'[Date Rank]), FILTER( ALL('Table1'), 'Table1'[Month] = SELECTEDVALUE('Table2'[Month]) ) ) VAR _tablecalculation = CALCULATETABLE( 'Table1', ALLSELECTED('Table2'), 'Table1'[Date Rank] = SelectedMonth, 'Table1'[Status] = "Not Normal", 'Table1'[Previous Status] = "Not Normal", 'Table1'[Type] = "Task" ) RETURN COUNTROWS(_tablecalculation) Any help in the right direction is definetly appreciated!Why doesn't ALLSELECTED work? Slicer issue
Hi, I have the following ProductTable with the column Status: Status Done Ongoing Ongoing Done Ongoing Done Done Done Delayed Delayed I'm trying to create a card visualiation for each status (Done, Ongoing, Delayed) to show % of total. I have created three measures, this one is for Done (the others is simply a copy paste using Ongoing and Delayed instead): Var_DonePercentage = DIVIDE( CALCULATE( COUNTROWS(ProductTable), ProductTable[Status] = "Done" ), CALCULATE( COUNTROWS(ProductTable), ALLSELECTED(ProductTable[Status]) )) This works as long as I don't filter anything, see below. However when I select for example "Done" in the slicer, the others should be 0% to avoid confusion. How can I make this happen?
ALLEXCEPT and ALL function does not work as I expected in DAX
Hi Everyone, I'm trying to use All/Allexcept functions in some calculations for some case scenarios. I was working with Qlik & QlikSense before and this is easly applicable on Qlik but I could not manage to get this done on PowerBI. This may not be applicable on powerbi or I may not achieve to do it. Eveything explained on the last page(ALL & ALL EXCEPT Examples), but I will give summary here again. (PBIX file : GoogleDriveLink) Page : ALL & ALL EXCEPT Examples What I'm trying to do is, I want to put a few measure on a table and when I make a selection from a field, I want only some calculations get affected from that selection, and some others don't. For example, I will put Master Date as dimension and 3 different calculation on the table. I will make a selection from Service Category field and only 1th. measure will be affected from that selection and others don't. Then I will make another selection from Availability field, then 2.th measure will be affected from the selection etc... I have 4 tables. Appoinment, Marketing, Utilization, Availability. All those 4 tables merged into appointment table. Measure 1: Calculation gets affected from only determined fields in the all except function. Test Appointment Qty = CALCULATE(sum(Appointment[m_appointment_qty]) ,ALLEXCEPT(Appointment ,Appointment[dim_country_id] ,Appointment[dim_country] ,Appointment[dim_master_date] ,Appointment[dim_service_category] ,Appointment[dim_promise_met]) ,Appointment[table_type]="Appointment" ,Appointment[dim_calculation_type]="Original" ) Measure 2: Calculation will not be affected from the determined field selections. Test2 Appointment Qty = CALCULATE( sum(Appointment[m_appointment_qty]) ,ALL(Appointment[data_flag]) ,ALL(Appointment[Availability]) ,ALL(Appointment[dim_day_diff]) ,ALL(Appointment[cleaner_id]) ,Appointment[table_type]="Appointment" ,Appointment[dim_calculation_type]="Original" ) Those calculations works fine if I don't put any dimension into the table but it does not work if I put any dimension. I tried it with and without FILTER function in the calculation but I could not manage this to work with FILTER function. When I put FILTER in dax formula, FILTER function does not allow me to put multiple dimensions to tell the calculation not to affect from any selection. More detailed explanation and use cases are on the page in PBIX file. I'd be so happy if you see the file and help me to write the correct DAX formula. Thanks for your support already. Table 2 Works fine but Table 1 will not be shown if I make any selection. I attached the uses cases as well.
Prevent Offset being filtered
Hi, I'm trying to create a measure for compared to same period last year (I can't use the normal time intelligence as we use 13 periods in the year). I am using offset to get the value for 13 periods prior, which works perfectly until I try to filter the data - it blanks. I've tried replacing the ALLSELECTED with ALL but then the OFFSET breaks. CALCULATE( [% Accum YTD Joiners (Heads)], OFFSET( -13, ALLSELECTED('Reporting DimDate'[Short_RSP],'Reporting DimDate'[Short_RSPYear]) ,ORDERBY('Reporting DimDate'[Short_RSPYear], ASC ) ) ) I want to filter this by DimDateKeep results from a matrix visual when using slicers and a measure with ALLSELECTED
Hi, I have this visual matrix where I show some results from a measure: The measure used is: Measure = SUM('Table1'[Results]) / CALCULATE( SUM('Table1'[Results]) , ALLSELECTED('Table1'), VALUES('Table1'[Date])) What I need is, when I use a slicer for the column "Shipper" and select a value, I need to keep the results of the previous image, (is the scenario where I don't select any slicer value): When I select a value from the slicer, the matrix will return this: The Table1 is something like this (the 'results' are just examples): Shipper Date Results SHIPPER 1--- jul/22--- 102 SHIPPER 2--- jul/22--- 408 SHIPPER 3--- jul/22--- 502 SHIPPER 4--- jul/22--- 42 SHIPPER 5--- jul/22--- 4 SHIPPER 6--- jul/22--- 21 SHIPPER 7--- jul/22--- 525 SHIPPER 1--- ago/22--- 78 SHIPPER 2--- ago/22--- 213 SHIPPER 3--- ago/22--- 210 SHIPPER 4--- ago/22--- 802 SHIPPER 5--- ago/22--- 20 SHIPPER 6--- ago/22--- 17 SHIPPER 7--- ago/22--- 43 etc...--- etc...--- etc... Thank you for your help, community,
