COVID-19 reporting Employees and Key Workers Report
Hello Complete PowerBI and DAX newbie, but am tasked with identifying how many of our staff are available for work during the Coronavirus Pandemic - as many are key workers supporting hospitals. I was trying to avoid nested IF statements, but have been getting a bit bogged down in when to use Switch with measures and calculated columns. I downloaded DAX Studio - but then quickly realised that the scripts are not directly transferable between PowerBI and DAX Studio. I have created: Absence Status Can Work = SWITCH(TRUE(), 'COVID-19'[Covid-19 Status ]="SA-Confirmed Case",0, 'COVID-19'[Covid-19 Status ]="SA-Suspected Case", 0, 'COVID-19'[Covid-19 Status ]="SA-Self-Isolating No Symptoms", 0, 'COVID-19'[Covid-19 Status ]="STL-Dependency", 0, 'COVID-19'[Covid-19 Status ]="STL-Other WFH",0, 'COVID-19'[Covid-19 Status ]="STL-Self-Isolating WFH",0, 'COVID-19'[Covid-19 Status ]="STL-Unable to WFH Equipment",0, 'COVID-19'[Covid-19 Status ]="STL-Unable to WFH Role",0, 'COVID-19'[Covid-19 Status ]="STL-WFH Suspected Case",0, 'COVID-19'[Covid-19 Status ]="",1 ) and Absence Dates Expired = SWITCH(TRUE(), AND('COVID-19'[Covid-19 Status Start Date]<>BLANK(),'COVID-19'[Covid-19 Status End Date]=BLANK()), 0, 'COVID-19'[Covid-19 Status End Date]<TODAY(), 1, AND('COVID-19'[Covid-19 Status Start Date]=BLANK(), 'COVID-19'[Covid-19 Status End Date]=BLANK()),1, 'COVID-19'[Covid-19 Status End Date]>=TODAY(), 0 ) However, I cannot then discover how to make the connection to combine my 2 results to match the business logic below. I had wanted to avoid hard-coding text into my variables - as the 'business' is a moving target at the moment. At the end of the day I need to give a total number of 'available' employees, and then I can illustrate who we have as 'key workers' for times of pressure. I would be very grateful for any assistance, so that I can return to feeding my family and getting outside for an hour! many thanks. COVID-19 Status Business Logic Status Can Work Absence Period Start Date Absence Period End Date Absence Period Expired= TRUE Can work if Absence Dates has an End date <TODAY, or if (No dates entered for Start and End Dates) STL - Other WFH Available by default. 1 0=FALSE/1=TRUE STL - WFH Suspected Case Available by default. 1 0=FALSE/1=TRUE STL - Self-Isolating WFH Available by default. 1 0=FALSE/1=TRUE SA - Self-Isolating No Symptoms Not Available, but becomes Available once 'absence period' (End Date) expired 0 0=FALSE/1=TRUE SA - Suspected Case Not Available, but becomes Available once 'absence period' (End Date) expired 0 0=FALSE/1=TRUE SA - Confirmed Case Not Available, but becomes Available once 'absence period' (End Date) expired 0 0=FALSE/1=TRUE STL - Dependent Family Not Available by default. 0 0=FALSE/1=TRUE STL - Unable to WFH Equipment Not Available by default. 0 0=FALSE/1=TRUE STL - Unable to WFH Role Not Available by default. 0 0=FALSE/1=TRUE
Display dynamic date range based on field parameter selection
Hello community, Client has a column chart in which they want to display a selected date grain (field parameter called 'Grain Selector'[Grain]) on the x-axis, and a selected measure (field parameter called 'Measure Selector'[Measure]) on the y-axis. Grain has 3 values: Year, Quarter, and Month. Measure has 2 values: Total Profit, and Total Units When they select Grain = Year, they want to show the measure for a set number of display years, say 1 When they select Grain = Quarter, they want to show the measure FOR A DIFFERENT NUMBER of display quarters, say 3 Ditto for Grain = Month I tried the following DAX measure (between rows of ***): *** DynamicRangeMeasure = // Config periods to display for each grain VAR _DisplayYears = 1 VAR _DisplayQtrs = 3 VAR _DisplayMnths = 6 // Grab FParameter selections VAR _SelGrain = SELECTEDVALUE('Grain Selector'[Grain]) VAR _SelMeasure = SELECTEDVALUE('Measure Selector'[Measure]) // Compute results VAR _Year = CALCULATE( _SelMeasure, DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -_DisplayYears, YEAR ) ) VAR _Quarter = CALCULATE( _SelMeasure, DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -_DisplayQtrs, QUARTER ) ) VAR _Month = CALCULATE( _SelMeasure, DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -_DisplayMnths, MONTH ) ) VAR _ChosenMeasure = SWITCH( _SelGrain, "Year", _Year, "Quarter", _Quarter, "Month", _Month ) RETURN _ChosenMeasure *** I keep getting error: MdxScript(Model)(13, 17) Calculation error in measure...: Column [Grain] is part of composite key, but not all columns of the composite key are included in expression... Given my business objective above, how can I fix this measure?
Switch logic not pulling in column from another table
Hello, I am trying to adjust my Switch statemnt so that I do not have to tediously input vlaues when I have to update the dashboard. Above line "3" is my old logic. Line "5 " begins my new logic, where I am trying to say if the code = 1003, and the LOS O/E is < [P95] (which is coming from another table) then return "P95". Here is the [P95] column The error I get says "A single value for column "P95" cannot be determind. The relationbetween the tables are many to many (*:*) and being cross filtered in both directions.
Create a Performance Score based on Monthly Total AND Yearly Average
Hello PBI Community! Novice PBI/DAX user here humbly seeking assistance. I'm trying to add a SCORING assessment for this KPI based on monthly date bins and yearly average score. I have several KPIs I need to provide scoring assessments for, so am hoping for a scaleable solution 🙂 Any ideas appreciated. Here is a sample of what I am receiving. I've circled SOME of the values that should be either Successful or Outstanding: Snippet of PBI Report Here is the measure that calculates Avg Calls / AE (Person) / Month which is referenced in the IF statement. Avg Calls / AE / Month Calculation WHAT I TRIED I created a NEW TABLE (New Opportunities Created - SUMMARIZED TABLE) from the original table which contains row level detail (New Opportunities Created). Then I created a new column to calculate SCORE: Score = SWITCH(TRUE(), 'New Opportunities Created - SUMMARIZED TABLE'[Segment] IN {"Field Sales", "LTL"} && 'New Opportunities Created - SUMMARIZED TABLE'[#Opps]<3.5, "NI", 'New Opportunities Created - SUMMARIZED TABLE'[Segment] IN {"Field Sales", "LTL"} && 'New Opportunities Created - SUMMARIZED TABLE'[#Opps]>4.3, "Exceeds", "Meets" ) Below is the result. It works at the MONTHLY level, however, it is not working for the COLUMN and ROW TOTALS. I should add that I do have some data filters as well: AND for the FINAL piece! I need to provide a summary at the Manager level, which is based on the TOTAL YEARLY AVERAGE ("Total #Opps" column)Dynamic grand total problem depending on switch selection
Hello community, I'm unsuccessfully trying to show the dynamic Grand Total as the sum of the columns depending on whether or not I select the "Scenario" filter to show more measures. If I select one measure the total appears but if I select two or more selections it does not appear The reason is that the measures do not talk to each other using the "Scenario" selector which is detached from the model and is used within the measures via the SWITCH function. I insert the measure, for simplicity I remove some scenarios Sum_Scenario_Year = //example of recalled measures //ACT CY = CALCULATE(SUM('CAPEX FOR ENG'[SCENARIO_VALUE]),'CAPEX FOR ENG'[SCENARIO] = "ACT" , DATESYTD(TIME[Date])) // ACT - 1 = CALCULATE(SUM('CAPEX FOR ENG'[SCENARIO_VALUE]),'CAPEX FOR ENG'[SCENARIO] = "ACT" , DATEADD(DATESYTD(TIME[Date]), -1, YEAR)) // PCL CY = CALCULATE(SUM('CAPEX FOR ENG'[SCENARIO_VALUE]),'CAPEX FOR ENG'[SCENARIO] = "PCL" , DATESYTD(TIME[Date])) VAR Scenario_value =SWITCH(SELECTEDVALUE('MAIN BASELINE'[MAIN BASELINE]), "ACT CY" , [ACT CY], "ACT -1", [ACT -1], "PCL CY" , [PCL CY], ) RETURN Scenario_value I tried to insert a logic for the total but it takes the sum of all the measures without distinction from the selected ones //if (hasonevalue('MAIN BASELINE'[MAIN BASELINE]),Scenario_value , [ACT CY]+[ACT -1]+[ACT -2]+[ACT -3]+[FCT CY]+[BDG CY]+[PCL CY]+[EST CY]+[CMT CY]+[FTR + 1]+[FTR + 2]+[FTR + 3]+[FTR + 4]+[FTR + 5]) The model involved is this one I am not sure if this is possible, but any help regarding this issue is greatly appreciated! Thank you in advance! FEST
Define Shifts Where Weekend/Weekday Shifts Involve Different Hours
Hi, I am trying to create a column that determines if the event happened on a day or night shift. Here is the column that I am trying to use to determine the shifts. A week day shift is 8a-5p A week night shift is 5p-8a A weekend day shift is 8a-8p A weekend night shift is 8p-8a Of note, I found a similar thread where a user was trying to do this with a similar date column. When I used their switch formulaI received an error that the first 6, is unexpected. A shift = Mon-Friday 06:00:00-14:00:00 B shift = Mon-Friday 14:00:00-22:00:00 C shift = Sun-Fri 22:00:00-06:00:00 D shift = Sat and Sun 06:00:00-18:00:00 Shift = SWITCH ( TRUE (), AND ( HOUR ( Table[EventTime] ) IN { 6, 7, 8, 9, 10, 11, 12, 13 }, WEEKDAY ( Table[EventTime] ) IN { 2, 3, 4, 5, 6 } ), "A", AND ( HOUR ( Table[EventTime] ) IN { 14, 15, 16, 17, 18, 19, 20, 21 }, WEEKDAY ( Table[EventTime] ) IN { 2, 3, 4, 5, 6 } ), "B", AND ( HOUR ( Table[EventTime] ) IN { 22, 23, 24, 1, 2, 3, 4, 5 }, WEEKDAY ( Table[EventTime] ) IN { 1, 2, 3, 4, 5, 6 } ), "C", AND ( HOUR ( Table[EventTime] ) IN { 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17 }, WEEKDAY ( Table[EventTime] ) IN { 6, 7 } ), "D" )
"Hacking" a waterfall chart
Hello all, I have adapted a code that I found in the link below to do some waterfall charts : Power BI & DAX Tutorial: Waterfall charts with beginning and end states in 5 minutes - YouTube It works pretty well. The second part of the code generate a waterfall chart showing evolutions of places for trainings offered in the next four weeks. I would need to change the way in which “Disponibles” (available) is shown. As we are talking about training places still available, it should be better to show it in its position but in green and positive. I know this is not logical for a waterfall behavior, nevertheless, somebody would have an idea to do this? The code : %RempSes_PEB = VAR Tot_Proposees = CALCULATE(SUM('03_Sessions1'[Max]), FILTER('03_Sessions1', '03_Sessions1'[Sup erreur ope]=BLANK() || '03_Sessions1'[Sup erreur ope]="X Nombre minimum de participants pas attei")) VAR Perdues_AnnulSession = CALCULATE(SUM('03_Sessions1'[Dispo]), FILTER('03_Sessions1', '03_Sessions1'[Sup erreur ope]="X Nombre minimum de participants pas attei")) VAR Perdues_Sessmaintenues = CALCULATE(SUM('03_Sessions1'[Dispo]), FILTER('03_Sessions1', '03_Sessions1'[Sup erreur ope]=BLANK())) VAR Inscrits = CALCULATE(SUM('03_Sessions1'[Inscrits]), FILTER('03_Sessions1', '03_Sessions1'[Sup erreur ope]=BLANK())) VAR selectedBreakdown = SELECTEDVALUE('%RempSess_Breakdown PEB'[Bloque]) VAR SelectedCategory = SELECTEDVALUE('%RempSess_Categories PEB'[Categorie]) VAR SelectedTime = SELECTEDVALUE('03_Sessions1'[Type session]) RETURN SWITCH(SelectedTime, "Passé", SWITCH(SelectedCategory, "Proposées", SWITCH(selectedBreakdown, "Perdues Annul session", Perdues_AnnulSession, "Perdues sessions maintenues", Perdues_Sessmaintenues, Tot_Proposees ), "Inscrits", SWITCH(selectedBreakdown, "Perdues Annul session", 0, "Perdues sessions maintenues", 0, Inscrits )), "Futur", SWITCH(SelectedCategory, "Proposées", SWITCH(selectedBreakdown, "Perdues Annul session", Perdues_AnnulSession, "Disponibles", [%RempSes_PdispoF], Tot_Proposees ), "Inscrits", SWITCH(selectedBreakdown, "Perdues Annul session", 0, "Disponibles", 0, Inscrits ) )) The measure used %RempSes_PdispoF = CALCULATE(SUM('03_Sessions1'[Dispo]), FILTER('03_Sessions1', '03_Sessions1'[Témoin suppression]<>"X")) Some data if needed : https://www.dropbox.com/scl/fi/phx9ydfuizuu0liwrveh3/Sessions-Waterfall-chart.xlsx?dl=0&rlkey=gr68u6p5i1p9i8udzgc31vh3gDAX SWITCH FORMULA SHOWING INCORRECT GROUPING
Hi, I'm trying to create a DAX formula using switch. I need to group all negative numbers to display no_new_return|prev rtn. However, some negative numbers are falling into other groups designated for positive values. Example -41 is falling in other groups such as 0-21,22-30 etc. as highlighted in the below image. The formula works for numbers > 0 but the negatives are not being recognized. Any assistance is appreciated. formula : ORD_RCPT_TENURE_GROUP = SWITCH(TRUE(),'FF_ORDER_MST'[ORD_RCPT_TENURE]<0,"NO_NEW_RTN | PREV RTN",'FF_ORDER_MST'[ORD_RCPT_TENURE]>=0 && 'FF_ORDER_MST'[ORD_RCPT_TENURE]<=21,"0-21",'FF_ORDER_MST'[ORD_RCPT_TENURE]>21 && 'FF_ORDER_MST'[ORD_RCPT_TENURE]<=30,"22-30",'FF_ORDER_MST'[ORD_RCPT_TENURE]>30 && 'FF_ORDER_MST'[ORD_RCPT_TENURE]<=60,"31-60",'FF_ORDER_MST'[ORD_RCPT_TENURE]>60 && 'FF_ORDER_MST'[ORD_RCPT_TENURE]<=90,"61-90",'FF_ORDER_MST'[ORD_RCPT_TENURE]>90 && 'FF_ORDER_MST'[ORD_RCPT_TENURE]<=120,"91-120",'FF_ORDER_MST'[ORD_RCPT_TENURE]>120 && 'FF_ORDER_MST'[ORD_RCPT_TENURE]<=180,"121-180",'FF_ORDER_MST'[ORD_RCPT_TENURE]>180 && 'FF_ORDER_MST'[ORD_RCPT_TENURE]<=365,"181-365",'FF_ORDER_MST'[ORD_RCPT_TENURE]>365,"+365","NO_NEW_RTN | PREV RTN")
