Hi, I have a question regarding this expression... Salaries over 10k = CALCULATE ( [No Employees], FILTER(Employee, [Tot salaries] > 1000 )) >>>>> is there any way to make "1000" dynamic ? for eg. by using measure linked with dropdown? or range?
Can anyone help me with this measure please? This measure returns a result: *Survey Completed = CALCULATE(COUNTROWS(vwSurveyResultsSubBU),FILTER(vwSurveyResultsSubBU,vwSurveyResultsSubBU[Question]="chat wait time")) This one returns "blank" *Survey Completed = CALCULATE(COUNTROWS(vwSurveyResultsSubBU),FILTER(vwSurveyResultsSubBU,vwSurveyResultsSubBU[Question]="Advisor Satisfaction"))
2 года назад
Ok, somehow i will learn how to use CALCULATE function, but tell me please, how did you know i am watching this video on friday 😁
How can I filter all except 2 countries for example, I know that you can do it with "", but only for 1, but if I want all except those 2 countries it would be like for example: calculate(sum(values);filter(tablename;table[country]"China" and "Us"))??, something like that, please help, thank you.
Dear Ruth, When we say, the filter argument, which is within the calculate, has to return a Number Text Date And can not return a Table. But, suppose I filter, where dept="sales" Won't I get an entire Table, in return, where the dept="sales" Thanks
I think what she was saying is that the Calculate function (not the filter) has to return a number, text or date. Filter arguments in a Calculate function can be the following: 1. Boolean condition (Ex: [dept]="sales') 2. A one column table (Ex: ALL(DeptTable[dept])) 3. All rows for one table (Ex: FILTER(ALL(DeptTable), [dept]="sales"))
Your videos are really helpful and informative, Ruth! The way you use examples to demonstrate differences in these hard-to-grasp concepts is simply remarkable. Thank you from a great fan of yours :-)
what if I have a table where each Customer ID has a record of everything they purchased in a given month. I want to measure the revenue of users in August, that do not have a record in September. Anyone have any idea here?
Thank you Ruth, very good explanation, ,,,,,, if we want to see the same behavior as the calculate function in the filter function (Ignoring the filter context) we add the ALL function, and this is how the calculate filter internally work::: Salaries Pending w filterALL = CALCULATE([No Employees], FILTER(ALL(Employee),Employee[Status]="Terminated"), FILTER(ALL(Employee), Employee[Dept.]="LA"))
Hi, I am writing the same Dax in visual studio, making a Tabular model cube, but I am getting the error. Servers:=CALCULATE([CompTCt],FILTER(ADComputerInfo,ADComputerInfo[ComputerType] ="Servers"||ADComputerInfo, ADComputerInfo[ComputerType] ="DomainControllers"))
Hi Curbal, how do I use calculate to compare 1 value against the whole table? eg, an agent score (filtered at page level) against all agents in the table. table(sales) structure looks like this: AgentID, Score, DeptID, TeamID
Dear Ruth, Thank you very much for the wonderful tutorials you have on DAX, really appreciated. I need some help :) I need a little more explanation of using slicers with Calculate Function and Filter Function. In this video, which you used the slicer, the function, which did not have the filter function, did not listen to the slicer, and the function which had the filter function did. However, i have another file, in which, when i user a calculate function (without a filter function within) and use a slicer, it works, I mean, the slicer does affect the output. for Example: Calculate Red Sales = CALCULATE( [Total Sales], 'Product'[Color]="red" ) f_Calculate Red Sales = CALCULATE( [Total Sales], FILTER('Product','Product'[Color]="red") ) When I used the slicer on Product Category, both of them changed to give me the same output. So, if you could give me an example of using Calculate, FIlter With Slicers, would really really appreciate, or if there is a video which explains the same. I also, checked the other calculate video's but did not get the answer to my question. Thank you very much for the wonderful work done. Best Wishes
Ruth, :)) I think I got the answer. If I filter on the basis of a column, which has been used in the dax, that specific filter will get ignored, since the filter mentioned in the calculate will override it But, if I filter on another column, which is not mentioned in our DAX formula, that will be accepted. This example, is given under the CALCULATE video. Have i got the explanation correct ? { Thanks Again }
Dear Ruth, Thank you for your prompt reply. Yes, indeed i saw the tutorial. Then after watching and practising 2-3 times, it worked. My query is resolved. ( I think while writing the entire msg to, it clicked what was going wrong) Thank you for the amazing tutorials and explanation. Keep up the amazing work. :))
Thanks for this video, a small clarification obout CALCULATE with Booleans; (more than one column in Boolean) Conditions of (> >= < = CALCULATE (expression; FILTER (table; AND(ALL(Boolean column1); ALL (Boolean column2))) because The filter arguments of CALCULATE (from the second parameter onward) are evaluated in the original filter context. according to that, CALCULATE + Boolean (column) = CALCULATE ( FILTER ( ALL (‘Table’[column]) ) ) Hope this helps, Regards!
Check the 25 days of dax challenge. Plenty of examples there that hopefully cover your case too: curbal.com/25-days-of-dax-fridays-challenge-ed1-northwind-company
I would like to add that when you need to combine filter expression + USERELATIONSHIP you mostly need to use version without FILTER, otherwise your measure will return null.
Thanks for the great video Ruth !! My marker was always: FILTER filters the table behind the scenes and CALCULATE overwrites it. Have a great weekend !
Hi Ruth Thanks for the great video! Is there a way to present the Tables (and charts) in Right to Left view? Could not find any info. For the right to left lengugages. Such as Hebrew or Arabic. Will Happy to read your answer. Thanks!
Hi! According to this: ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8267295-right-to-left-chart-and-dashboards-supports It is already done, but for what I can see on google it is not supported yet. Your best bet? Vote on the ideas.powerbi.com and promote so others vote too. Happy Friday! /Ruth
Hi, I am trying to generate previous month sales in a matrix view. When I use PMSales = CALCULATE([TotalSales],FILTER(Datestable,DATEADD(Datestable[Date],-1,MONTH))) I can see current month values only. However when I use PMSales = CALCULATE([TotalSales],DATEADD(Datestable[Date],-1,MONTH)) it displays the previous month sales. What is the difference between the two measures??
Hello, big fans of your lectures thanks i am facing one issue where my client want to display last 5 finacial qaurters including current quarter. their financial commence from feb to jan so if current fy quaerter is 2020 Q3 the. data in table should be for 2020 Q3, 2020 Q2, 2020 Q1, 2019 Q4 , 2019 Q3 and corresponding measure in table can you please help me with this or if you have uploaded any video for such scenario plz let me know Thank you
Hi there, very informative, Have one concern. What if I want to use OR function in Filter? in above example we have used 2 filters, 'Status; AND 'Department' . Which mean we are indirectly using AND function using 2 filters. What if I want to use OR... For example , Status is Pending OR Terminated.. How should I get these details thank you
Hi, just curious. Can you create a slicer that filters 2 values? Lets say I have a STAtus column with 0,1,2 data. My slicer says Active, Pending, Inactive. When i select Active it should select all 2 and 1 status. When i select Pending it should filter all 1 status. When i select Inactive it filters all 0 status. Thanks
Hi Ruth!, thanks for all your videos!! I have a doubt, can i use a wildcard when i use calculate ?? something like "pend*" when i want to filter all the words that start with "pend"? Saludos! Arica-Chile :)
Hi Ruth! Many thanks for the explanation of the Filter function. I tried to apply it by excluding the zeros in the median. However this does not work: Median= CALCULATE(Median(TableName[SaleAmount]), FILTER(TableName, TableName[SaleAmount] 0 )) . It always says that there are too many arguments. Do you have any idea why? Many thanks for your help in advanced
Hola Ruth, que tal tengo un par de consultas con respecto al objeto Matrix de Power BI , quería saber si puedes: - Darle formato a cada uno de los encabezados de forma independiente: Cambiar el color de fondo o el color de fuente - Solo habilitar la opción de icono +/- solo para algunos encabezados. Muchas Gracias por la ayuda
Can you make a video on how to filter a specific column from a multicolumn table or the opposite not making that specific column change when filter is applied. Thanks
How can I do if I need a measure that lets me filter by measure? I have a measure that needs to be a measure because is dynamic depending on parameter. Now i want calculate the count of values > 0 for that measure and i cant figure it out I need it to be a measure because i then use that total count for other calculations.
Hi, thank you very much for these series of videos, I am learning a lot indeed. Requesting one small URGENT HELP from you today. If you kindly help me by replying that would be truly helpful for me. Dax formula in Power BI --> Salaries Pending = CALCULATE([No Employees]), Employee[Status]="Pending", Employee[Dept.]="LA") ### My question: How to exactly write this formula if the "Status" column values in each row contains not just the word "pending", instead it contains phrases like "pending for Jan" or "Feb salary pending for employee"? In other words using this formula how can I count rows those will match criterias even though word "pending" or word "Terminated" will be inside a phrase in some rows instead of a single word? Should I use wildcard characters like * ? Could you please urgently help me with the your kind suggestion and correct formula to achieve my requirement by tomorrow (Monday 21-may-2018)? I requesting because your kind little help me in my project a big way. Hope to receive your kindness and help urgently. Thank you.
Yes Mam, thank you very much for your kind quick support. I am going through your suggested video now, and will try to get it done. I will update you on my status of learning shortly.
Hi Mam, I have gone through your Video # 30, which is very helpful in this regards. I understood how to use wildcards to find particular keywords inside phrases. However, I would like to request your one more guidance please. You see, below SEARCH formula will help me to populate New Columns' particular rows with suggested keywords, and which means I would need to create multiple columns to understand how many of each KEYWORDS are available in a particular column by running another CALCULATE(COUNTA( formula. So, to populate each Keyword based columns, Formula1 would be: Highlighting Keywords = SWITCH(TRUE(),SEARCH("*Terminated*",Employee[Status],,0)>0,"Terminated",SEARCH("*Pending*",Employee[Status],,0)>0,"Pending") And then, to count each of those Keywords, Formula2 would be: Count of Terminated keyword = CALCULATE(COUNTA(Employee[Terminated Keyword],Employee[Terminated Keyword]="Terminated") where "Terminated Keyword" is the column name I had additionally added to capture & populate word "Terminated" by running above mentioned SWITCH formula1. My Question: How to mix these 2 formula(s) into 1 formula, so that once Formula1 find out the Keyword (example: Terminated) it will totally count that and will type out the counted number only as Output? Means how to mix these two formula together? What should be that mixup single formula? If you kindly help me with this suggestion and kindly help me to write that One mixing formula, then I will be able to save at least 4-5 hours for sure in my project. Hope to receive your kind guidance and help on this urgently. Thank you.
Thank you once again. :) Could you please guide with your expert suggestions on how to add above mentioned 2 formula(s) into one formula to achieve the result as I had described above?
Hi Tim, No of employees is COUNTA(Employees (name)), I will add the download file in a hour . Of course, if you want to count the number of employees on your file use DISTINCTCOUNT instead so you get the right number. /Ruth
Thanks Ruth. This is great stuff for the learners. I have taken a paid course from very renowned trainers and they also haven’t clarified it. May be they expect me to know in advance. Thanks again.
Great video as always ! I have a question though: Is there a way to filter by using only part of the value and not the whole word? For example instead of finding all products for which [Color] ="Red", can we find all products for which [Color] contains the letter "R" somewhere in the color field?
So if I want my result to be dynamic, I really need to add FILTER function(s) in the CALCULATE function. Thanks for pointing out the difference with and without FILTER, Curbal!
Hi Ruth , as you said it does not contain measure but you used measure "no employees" , is this measure or column, calculator sign shows it is measure. My other question is that how can I register myself for Power BI community. I have Power BI installed but I cant register they said you need admin right to register it. Is there any other way to register in power bi community.
Ok, got it! Lock aspect means that if you change one dimension (height or width) of a visual, the other dimension will automatically be changed to maintain the shape and proportions of the object. Hope this helps! /Ruth
Hey Ruth! Please, help. I try to get the files using the link curbal.com/blog/glossary/calculate-dax but it shows me impossible to download the file (s)
@@CurbalEN Thank you for the prompt response, ma'am. My report has 15 parameters to flex the inputs. And the report is getting slow after all calculations and visuals are done. Calcs are not that complex except one or two measures. And the parameters have 50 inputs on an average for each parameter. Could you share your insights on this problem?
I think I gotta start taking notes on your videos for a quick glance for myself. Your videos have always been consistently good even it was produced many years ago. Thanks, Ruth!
Not good enough in those languages to make videos about it, but when I learn something I always show it here. There is an R and Python playlist, check it out! /Ruth
Hi Ruth, thank you for your lovely videos. I am trying to count the number of distinct cities where profit is greater than 100,000. When I use the calculate function, it does not aggregate the profit over distinct cities, what is the work around? In sql it will be aggregate by sum of price and group by city and having clause having sum(price) > 100000.
I am a hopeless optimist and a dreamer so assuming that Microsoft watches my videos and that pleading to them would work it is right up my alley 😂😂😂 /Ruth
