Measure that filters a table to a single row and returns text from a specified column (Excel365)
Hi there I still have a lot to learn about DAX, and have become stuck trying to develop a measure that will filter down my source table to a single row by using values in two columns and return the (text) value from a different, specified column. I have two measures that filter a table and calculate the expected values for (a) the oldest date for an unresolved service ticket and (b) the corresponding number of days elapsed after that date, but what I am unable to do is to retrieve the text value from a column for the row that table is calculating the age and date from. I've been going around and around in circles researching and trying different functions for far too long and am super confised now. I would really appreciate some help! 🙂 Most of my attempts at developing a measure result with an error message advising ~ that multiple have been supplied where a single value was expected, but by now I have failed in dozens of different ways. I've knocked up a file with dummy data and measures for reference - hope this helps with understanding my request and developing a solution. The pivot table I'm working with is used to generate statistics that are consolidated and presented as part of a dynamic and interactive 'dashboard' on another sheet; it is the "SYS" values that I am trying to populate with this measure: The image below is from the sample file provided, with the column in pink manually created to illustrate the desired result. Thanks in advance for your help!🤗
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.
ignore column value filter in a table visual
hello i have visual table with below fields' I'm trying to calculate the agent achievement which is made of 1- core achievement measue (which equals 90%* by new achiev% measure) 2- accessories achievement (which equals 10%* new achiev% measure) 3- total achievement (equals the sum of core achievement and accessories achievement) as you can see from the table above, i managed to to calculate the first 2 parts for core achievment and accessories achievement. the problem is when i was trying to sum the two percentages it's not working. as for [category type]= 'accessories' it will show blank in core achievment measure and for [category type]='Core' it shows blank for accessories achievement. i want to show the accessoreis achievemt '7%' instead of blank for category type ='core' so that for each core category i can sum both core and accessories achievement as below
Dynamic measure - filtered month
Hi all, Master Table: main tablewhere I have the values "Balance" per each Month/Currency. Fx Historical Rate: table that report all the exchange rates. I need to calculate the "Monthly Sales €", for example of March, as the difference betwwen YTD 03 @ 03 Act Rate - YTD 02 @ 02 Act Rate. This measure have to be dynamic, so if I filter "03" in the "Month Filter" I want to see 372 in the table. 4.660 If I filter "02" and so on. Each balance amount have to be converted using the correct exchange rate. Can you help me? Thanks in advance
How to make Fixed Totals & Subtotals (filter independent)
How to make Fixed Totals & Subtotals? Example (two identical matrix - just row order is different): Problem: when I filter by year or product, subtotals are recalculated, but I want to see global fixed subtotals (by another words the same subtotals as on screen above) Is it possible? Download Source File
SUMIFS in DAX without relationship
Is there any way where we can use SUMIFS in DAX without the relationship present. I have two tables Table 1 Table 2 I know for a fact that most of them would ask me to relate the table above as there is one to many relationship. If there are multiple columns and I have to do validation based on criterias then I would use SUMIFS. Can anyone help me down with the expression for bringing values from table 2 to table 1?
