where
2 Topics
DAX Issue
Hi, I have the following DAX (credit https://addendanalytics.com/blog/calculate-working-hours-in-power-bi) which creates a time from hours and minutes between 06:00 and 23:59 and I want to make a small change: var Hours_List = SELECTCOLUMNS(GENERATESERIES((6), (23)), “Hour”, [Value]) var Minutes_List = SELECTCOLUMNS(GENERATESERIES((0), (59)), “Minute”, [Value]) var two_years_ago_start = DATE(YEAR(NOW())-2,1,1) //DATE(2018,1,1) var one_year_later_end = DATE(YEAR(NOW())+1,12,31) var Dates_List = CALENDAR(two_years_ago_start,one_year_later_end) var HoursandMinutes = ADDCOLUMNS( CROSSJOIN(Hours_List, Minutes_List), “Time”, TIME([Hour], [Minute], 00), “Validity”, IF([Hour]<6 || [Hour]>23,”Non working”,”Working”) ) I would like the list to stop at 23:45 but only for hour 23. I want the window to be 06:00 to 23:45 but still count the last 15 minutes in all other hours. My instinct is to use an IF on the ADDCOLUMNS function used in setting the variable HoursandMinutes but I'm not sure exactly what it would look like as I am used to managing these changes in SQL where I would say: WHERE NOT (Hours_List = 23 AND Minutes_List > 44) Any help would be wonderful Thanks in advance MattSolved3.5KViews0likes3Comments
Help With DAX Query
Hi! I would like some help with writing a DAX query. I have 2 tables, one with customers details and the other with calls details that the customers made. I am trying to find the customers who did not made any calls. in SQL I found the answer with this: select distinct c.KeyCustomer from DimCustomers as c left join FactUsage as f on f.KeyCustomer=c.KeyCustomer where f.CallId is nullSolved1KViews0likes2Comments