Total Is Correct but row is not when using ALL
Biggest puzzler yet: I have this measure called [%Need]. The sum total of these percentages needs to be 1 in order to compute correctly. However, it's not, so in order to correct it I need to do a formula like: [%Need]/Sum [%Need] *1. So in order to get sum portion I did: CALCULATE(SUMX(VALUES('Calendar'[calendar date]),[%Need]),ALL('Calendar'[CalendarDate])) This puts 1(I checked it out to 15 0s 1.00000000000000 in each row instead of the correct amount which is 1.0147. 1.0147 however appears in the Total at the bottom. I need 1.0147 in each row so I can have it divided by [%Need]. This probably doesn't need to be included, but just in case the [%Need] measure is: IF([CALendarDATE]>LASTNONBLANK ( 'Calendar'[Calendar Date], [Gross Adds]),[PYGA]/([PYALLGAs]-[GAsLyMAXDateAll]),Blank()) Any idea how to get the correct total in each row?
ALL function disabled when applying date slicer
Hi, I'm new to power bi, and i'm trying to calculate the OEE in a company. I'm supposed to calculate the availability for every equipment, then the performance and quality for every component produced and then the OEE. In that way, i need several slicers to apply the specific filters and calculations i need. So, basically, i need the availability to make the calculation regarding the equipment, but then ignore the selected component. For that i use an All function im my measure to ignore component. The calculation is done correctly if i don't have the week interval selected(semana), has you can see below. If i change the selected component the availability stays the same, as it should, but if i change the week slicer to make the calculation for a specific week or weeks of the year, then everytime i change component in the slicer the availability slicer changes as well. I don't understand why, but for some reason the week slicer must be disabling the all function. Here's my measure, you can ignore most of it. It just changes the calculation depending on que equipment selected. #Disponibilidade = CALCULATE( IF( SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11197" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11198", // Equipamentos que trabalham 24 horas por dia util e 9 horas ao fim de semana (SUM('dados'[Tempo Seg (con. média)]) + SUM(Paragens[Tempo Paragens(segs)])) / ([#Número Dias Úteis]*24*3600 + [#Numero de dias(fds)]*9*3600 + CALCULATE(SUM('dados'[Horas Extra(s)]),FILTER(dados,dados[Dia da Semana]= "Saturday" || dados[Dia da Semana]="Sunday"))), IF( SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11140" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11195" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11196" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11200" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11201" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11203" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU11204", // Equipamentos que trabalham 24 horas por dia, dias uteis w 24 horas fds (SUM('dados'[Tempo Seg (con. média)]) + SUM(Paragens[Tempo Paragens(segs)])) / ([#Numero de Dias]*24*3600), IF( SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU13100" || SELECTEDVALUE('dados'[Nº 1º Equipamento]) = "EQU13051", // Equipamentos que trabalham 24 horas dias uteis (SUM('dados'[Tempo Seg (con. média)]) + SUM(Paragens[Tempo Paragens(segs)]) ) / ([#Número Dias Úteis]*24*3600), (SUM('dados'[Tempo Seg (con. média)])+ SUM(Paragens[Tempo Paragens(segs)]) ) / ([#Segundos Uteis] + SUM('dados'[Horas Extra(s)])) // Equipamentos que trabalham 8 horas por dia, dias uteis ))), ALL(dados[Nome Artigo Prod.]) ) Here's my tables with their relations: In this measure only the dados and paragens tables are used.
All doesn't override filter CONTEXT
Hi All, In the image here, I am trying to get All to override filter context on category. However, that doesn't happen. It works for every other column like Brand, color or manufacturer. However, it doesn't remove filter context for category and subcategory. I even tried using the category field which is within the hierarchy but no success.DAX too Slow with Filtering
Hi, I have the following measure: Measure = CALCULATE ( COUNT ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), 'Table'[ValidFromDate] < MAX ( 'Date'[Date] ) && 'Table'[ValidToDate] > MAX ( 'Date'[Date] ) ) ) The two tables (Table, Date) are not connected. I use this to see "Active" contracts on a specific date. However when I place it in a visual to see it on a daily basis (last 12 months for example), it is way too slow (I have 12 million rows). Is there a way to optimize this? Much appreciated!
Few Slicers to work on measures and few are not
Hi, I am facing an issue with creation of DAX. I have got a request from the client where we have a slicer of an Item and Provider at the top of dashboard. I have to show all to KPI for that Item and Provider selected. Further I have all the other dimension filters like Country, City, Corporate group etc. Then I have to create a DAX wherein I have to find the median of my measure amount of the selected Item and Provider. Another measure I have to create is Median of Rest of the Providers amount that were not selected in that Provider slicer. All the dimension filter should work on the rest of the filters but not on the selected filter. Also, I have to show all the slicers so removing interaction is not helping. I have to write the DAX only. Any type of help would be very much appreciated. Thank you.DAX to get MIN/MAX date from a slicer but ignoring date in matrix
Hello, The slicer and the matrix in the screenshot below both reference the same date table. The Date table has every day between 1/1/1995 to 12/31/2024. How can I get the MIN and MAX date from the AsOfDate slicer and reference in the matrix? If I just calc min/max date it only displays MIN/MAX for the current year - as is grouped in the matrix. If I use ALL it removes all filters and doesn't even use the slicer filter dates. So, I'd want the calc to display (MIN) 5/13/2006 in every cell in the matrix - and MAX of 3/20/2019. This is needed for financial calcs we are performing. Appreciate any assistance you can provide. Thanks, DanDAX REMOVEFILTERS failing on a measure
Hi all i am using may update version and a formula is failing. My problem is very easy to explain I want to fix a calculation to get the average of distance FOR ALL DATES ( so I need to remove filter on this DATE field, but if I add to the page a slicer, the measure changes. I want to remove explicitly filters. Here is the sample PBIX to test what I mention https://1drv.ms/u/s!Am7buNMZi-gwg8hT4eo1bkHP975Egg?e=goyfU7 My problem is REMOVEFILTERS is not working fo TableGPS[DATE] column and also not working for TableGPS[DRILL] In the below formula REMOVEFILTERS is not working, I also tried with ALL(TableGPS[DATE],TableGPS[DRILL]) Formula DISTANCE TOTAL AVERAGE MD v1 = CALCULATE( AVERAGE(TableGPS[DISTANCE]), TableGPS[Type]="MD", TableGPS[DURATION]>=70, REMOVEFILTERS(TableGPS[DATE],TableGPS[DRILL]) ) Image1 - If I don't filter any date I get the value correct. In this table we get the correct value of 9940.07 for the measure. Image2- However after filter 21/03/2023 the value CHANGES and a I want to get the value 9940, my measure needs to avoid the filter by DATE column and also by DRILL Column. thanks in advanceDAX REMOVEFILTERS and ALL not working on date column
Hi all i am using may update and a formula used to work in report similars is not working now. My problem is very easy to explain I want to fix a calculation to get the average of distance FOR ALL DATES ( so I need to remove filter on this DATEF field, but if I add to the page a slicer, the measure changes, this was working in an old Report, I think It can be something of the new powerbi may 2023 update) My problem is REMOVEFILTERS is not working fo TableGPS[DATEF] column and also not working for TableGPS[DRILL] version1 DISTANCE TOTAL AVERAGE MD = CALCULATE( AVERAGE('TableGPS'[DISTANCE]), TableGPS[Type]="MD", TableGPS'[DURATION]>=70, REMOVEFILTERS(TableGPS[DATEF], TableGPS[DRILL]) ) version 2 with ALL also not working DISTANCE TOTAL AVERAGE MD = CALCULATE( AVERAGE('TableGPS'[DISTANCE]), TableGPS[Type]="MD", TableGPS'[DURATION]>=70, ALL(TableGPS[DATEF], 'TableGPS'[DRILL]) ) Regards
Using two date dimensions not working
I want to have a graph with two lines showing sales data from the prior and current year. I want one line to just be a straight line across which is the prior year sales multiplied by a fixed number representing a percent increase. The other line will be the cumulative sales to date by month for the current year. The report has a table which shows the current year-to-date Average Daily Sales, and the prior year-to-date Average Daily Sales as of the same month in the prior year. Users can select a month from a date slicer, based on the date dimension DIM_PERIOD_GL_DATE, in order to view cumulative sales data as of the selected month in the current and prior years. For example sales data through April of the current year and April of last year, February of the current year and last year etc. I don't want the graph to be affected by which month is selected in the slicer. I created a second date dimension DIM_PERIOD_GL_DATE2 to be used by the graph and defined a measure like this for the straight line showing the prior year sales with a 3% increase: CFY Sales Goal = var yr = CALCULATE( MAX(DIM_PERIOD_GL_DATE2[FISC_YR_NUM]), FILTER( ALL(DIM_PERIOD_GL_DATE2),DIM_PERIOD_GL_DATE2[Date] = TODAY()-1) ) var pysls = CALCULATE( FACT_SLS[Sales AMT], FILTER( ALL(DIM_PERIOD_GL_DATE2),DIM_PERIOD_GL_DATE2[FISC_YR_NUM] = yr-1 ) ) return pysls*1.03 However, when I select a month from the slicer that uses DIM_PERIOD_GL_DATE it is Changing the value of CFY Sales Goal in the visual Shows a value only for the selected month even though the date in the visual is from DIM_PERIOD_GL_DATE2. Even if the measure was based on DIM_PERIOD_GL_DATE selecting a month shouldn't change the value of the measure. As expected the value doesn't change when I select various months from a slicer that uses DIM_PERIOD_GL_DATE2 (as long a month from the current year is selected). Why is the date slicer that uses DIM_PERIOD_GL_DATE affecting the visual?Apply ALL filter on a field - and then after that apply hard-coded filter on same field
Hello, We have a table of tags which joins back to various tables. How would I rewrite the code below to apply ALL filter to OneData_Tags field TagType. And then apply TagType = "Business Owner". So, first need OneData_Tags table where we remove all filters on field TagType. And then we apply a filter on that field where TagType="Business Owner". TagName_BusinessOwner_Grouped = CONCATENATEX(FILTER(OneData_Tags, OneData_Tags[TagType]="Business Owner"),OneData_Tags[TagName],", ") Any help greatly appreciated, Dan
