List of values with filter, kindly help me!
Hi Team, Kindly help me for filter visual. I have the 'Data' column and categories columns. like below snapshot format. we required filter, If I select categories col of 'a' then "a" related all data shown like below snapshot. If select 'b' then "b" related of all data required. How to it in DAX with this? Note:- In power query it is possible, by splitting muliple cols and we will do it. but have performacne impact, kindly help me to it using DAX?
Error with VALUES Function When No Filter is Applied and Issue with ADDCOLUMNS in DAX Measure
I'm trying to create a DAX measure that calculates reliability, but I’m encountering an issue. The goal is to make the measure work when one or more equipment items are selected. However, when no equipment filter is applied, it throws an error in the graph. I need help adapting the formula to handle this case properly. Here’s the original code: Confiabilidade_Sistema = VAR t = SELECTEDVALUE(Tabela_TTF[TTF]) -- Time to failure (in hours) VAR Equipamentos = VALUES(TTF[Equipamento Corrigido]) VAR Confiabilidades = ADDCOLUMNS( Equipamentos, "Confiabilidade", VAR Beta = CALCULATE([b_eq], TTF[Equipamento Corrigido] = EARLIER(TTF[Equipamento Corrigido])) VAR Eta = CALCULATE(EXP(-[a_eq] / Beta), TTF[Equipamento Corrigido] = EARLIER(TTF[Equipamento Corrigido])) RETURN IF(NOT(ISBLANK(Beta)) && NOT(ISBLANK(Eta)), EXP(-POWER(t / Eta, Beta)), 1) ) RETURN 1 - PRODUCTX(Confiabilidades, 1 - [Confiabilidade]) Now, I’m trying to adapt the formula to handle both cases — when equipment is filtered and when no filter is applied: Confiabilidade_Sistema = VAR t = SELECTEDVALUE(Tabela_TTF[TTF]) -- Time to failure (in hours) VAR Equipamentos = IF( ISFILTERED(TTF[Equipamento Corrigido]), VALUES(TTF[Equipamento Corrigido]), FILTER( VALUES(TTF[Equipamento Corrigido]), NOT(ISBLANK(TTF[a_eq])) ) ) VAR Confiabilidades = ADDCOLUMNS( Equipamentos, "Confiabilidade", VAR Beta = CALCULATE([b_eq], TTF[Equipamento Corrigido] = EARLIER(TTF[Equipamento Corrigido])) VAR Eta = CALCULATE(EXP(-[a_eq] / Beta), TTF[Equipamento Corrigido] = EARLIER(TTF[Equipamento Corrigido])) RETURN IF(NOT(ISBLANK(Beta)) && NOT(ISBLANK(Eta)), EXP(-POWER(t / Eta, Beta)), 1) ) RETURN 1 - PRODUCTX(Confiabilidades, 1 - [Confiabilidade]) -- Calculates system reliability However, it returns the following error: "The ADDCOLUMNS function expected a table expression for the '' argument, but a string or numeric expression was used." The issue arises when no equipment is selected. If any equipment is selected, the graph works fine. Could you help me fix this issue?Urgent Reverse Burndown Chart for Features in Quarter
Hi Everyone, For all the experts of Power BI community, please help me in this dax. I am trying to create a burndown chart for Distinct Features Projected Vs Distinct Features Planned. I have two separate datasets for each like below Feature Projected Dateset: Feature_Id End Date 1842179 05/14/2024 1730860 03/04/2024 1764030 03/01/2024 1757984 02/27/2024 1757947 03/05/2024 1757062 04/09/2024 1732445 03/04/2024 1872820 07/18/2024 1845193 07/18/2024 2162467 09/06/2024 2003900 07/18/2024 1960331 07/18/2024 1872804 09/06/2024 1867369 09/01/2024 Feature Planned Dataset: FeatureId Target Date 2216545 08/08/2024 2118272 07/20/2024 2093351 09/30/2024 783008 09/30/2024 1913126 09/22/2024 1366800 08/17/2024 2329963 10/07/2024 2303984 09/11/2024 2303975 09/11/2024 2251162 09/11/2024 2093348 09/30/2024 I have date dimension table and has active relationship with both datasets above. I Created dax but I am getting output as below in snap: Planned = CALCULATE(DISTINCTCOUNT('Committed Features'[FeatureId]), FILTER(ALLSELECTED('Calendar') , MAX('Calendar'[Date]) <= 'Calendar'[Date])) Projected = CALCULATE(DISTINCTCOUNT(Merge1[Feature_Id]), FILTER(ALLSELECTED('Calendar') , MAX('Calendar'[Date]) <= 'Calendar'[Date])) I am expected to get output as Below: Where count ends with zero and datapoints are showing good Could anyone please help me out here. Not sure where I am going wrong. Greg_Deckler community15 community24 @v-kongfanf-msftDAX query required for IF and IF Else condition of below case
Hi Team, I am new to PBI, help me with this case! IF type(col) = "AAA" & Date time(col) < 11AM then Pickup date = Date(col)-1 ELSE Pickup date = Date(col) IF type(col) = "BBB" then Pickup date = Date(col)–1 IF type(col) = "CCC" then Pickup date = Date(col) Value of "AAA" is having AAA_a, AAA_b, AAA_c, AAA_d, AAA_e ----AAA_n etc. So here we are calling the condition with "AAA". How do we calling "AAA" with one IF condition statement or any other. Same like value of "BBB" & Value of "CCC". Here date time(col) contains 24hrs, we required less than(< )upto 11AM then Pickup date = Date(col)-1, else Pickup date = Date(col). Here Date(col) is calculated column. So, kindly helpe me with the DAX query. below is the snapshot for your reference.Error in Grand Total, Selected Value Returning Wrong Total when 'Select ALL' is Clicked
Hi everyone, I need help in resolving multiple problems in my power BI visual. The objective of the visual is to perform a comparative analysis for the actual cost of the Project with the original vendor and compare it to all the possible alternative vendors available on each of the state. Actual Result: Expected Result: Here are the important features of my visual which I wanted to work well. Feature 1: When a user selects alternate vendors using the 'MV. Vendor Name' slicer, it should update the value of 'Distribution X' and show the number of the vendors selected. This will be the basis of the computation where the Actual Hours and Cost will be equally distributed according to the number of alternative suppliers selected. Problem: The measure for 'Distribution X' is working when the user would manually select on the 'MV. Vendor Name' slicer but once the user hits 'Select All' it will show all the possible suppliers ignoring the filters applied to the other slices (Project Name/State/Vendor) DAX Code: Distribution X = VAR TotalMV = CALCULATE(COUNTROWS('Project Matching'), ALL('Project Matching'[MV.Vendor Name])) VAR SelectedMV = COUNTROWS(ALLSELECTED('Project Matching'[MV.Vendor Name])) RETURN IF(TotalMV = SelectedMV, TotalMV, SelectedMV) Feature 2: After determining the cost and hour allocations, the Cost using the New Rates (NR Cost) is now computed via measure. This will be the basis later on for the row per row computation of the savings. Problem: The measure 'NR Cost' is calculating correctly on a row per row basis, however, I expect the grandtotal to sum all the rows, however my beginner knowledge is limited. I tried applying the ones in the forum but I think I haven't really understood how Sumx iterator could do really. Dax Code: NR Cost = VAR mv_rate = [MV Rate] * [Hrs_Alloc] RETURN IF( ISBLANK(mv_rate) || mv_rate = 0 , [Cost W Inflation]/[Distribution X], mv_rate) Feature 3: Line per line computation of the Savings/Overage using the measure 'Savings/Over' Problem: As you can see in the visual my code is not returning any value at all 😭 Dax Codes: Cost_Alloc = VAR SelectedMV = COUNTROWS(ALLSELECTED('Project Matching'[MV.Vendor Name])) VAR CurrentMV = SELECTEDVALUE('Project Matching'[MV.Vendor Name], "Default") VAR acounta = [Cost W Inflation] RETURN IF( HASONEVALUE('Project Matching'[MV.Vendor Name]), IF(SelectedMV <> 0, acounta / SelectedMV, BLANK()), SUMX(VALUES('Project Matching'[MV.Vendor Name]), [Cost W Inflation]) / SelectedMV ) Savings/Over = [NR Cost]-[Cost_Alloc] I attached my dummy data and pbix in the link, should you need more context about the matter. Power BI file and Excel File Thanks so much for the help!
calculated column is returning zero instead of value
hello i have created a calculate column that returns the percentage slap of a a measure. for example, if measure percentage = 120%, calculated column will return "120%-129%" this is the calculated column dax the output is always 0 for all measure[achiev of all] percentage values any idea how to fix the dax? please note that i have to create calculated columnn and not measure since i want to use the column to join with another tableStDev discrepancy when using similar tables
I wasn't exactly sure how to word my problem to make it stand out from similar but different issues. Anyway, I have a DAX table that I created and it works flawlessly but I was recently asked if I could add an additional column, a unique ID, to the table but when I did, it throws my StDev off, therefore throwing off my Z-Score and I'm not sure how to fix it. Below is the Expected vs Incorrect output columns. The measure I'm using for the Expected Output visual is as follows: StDev_24_TEST = CALCULATE(STDEV.P('24MonthTable'[Failures]), DATESINPERIOD('24MonthTable'[Month-Year], LASTDATE('24MonthTable'[Month-Year])-1, -24, Month ) ) I tried using the same measure for my "duplicate" table but it returned all 0's. I tried several different methods and all but one variation returned 0.0. The measure that "somewhat" worked, and I use that term very loosely was: STD24 = VAR s = CALCULATE( STDEV.P('TEST'[Failures]), ALL('TEST'), -- Remove any other filters on 'TEST' DATESINPERIOD( 'TEST'[Month-Year], LASTDATE('TEST'[Month-Year]) - 1, -24, MONTH ) ) RETURN s The ONLY difference between the tables is one column, [PR ID] which I was planning on using to link to other tables because it's a unique identifier.
