Convert SAP BO measure with Foreach() operator to DAX formula
Hi I'm pretty new to SAP BO and I'm trying to convert a measure that I've found in a report to DAX language. The foruma uses the context operator FOREACH() on two columns ([Data].[KPI Group] and Data].[KPI Name] ) and I don't know how to write it in DAX. Could you please help me figuring out? Here's the code: SUM( IF( [Not Available] FOREACH( [Data].[KPI Group];[Data].[KPI Name] )="Y" ) THEN 1 ELSE 0 ) Thanks
If statement with wildcards
Hello, I have about 15 years of data broken out into 4 categorys based on season FA, WI, SP, SU. I am attempting to label each label as Fall, Winter, Spring, Summer. The data looks like 2020/FA, 2019/FA, 2018/FA...etc for all seasons. Currently I am doing an endless if by IF(Table = "2020/FA", "Fall", IF (Table = "2020/SP", Spring, IF.... This is turning into a very long and repetative processs and I was experimenting with wildcards (*,?) trying to be able to get, IF(Table = "????/FA", "Fall", IF (Table = "????/SP", Spring, IF...., but neither * or ? was getting picked up as a wildcard. Should the wildcard characters be outside of the "" or am I having my logic wrong any help is appricated, does not have to be an if statement.
DAX formula to show the Attendance date greater than equal to System date using -UTCNOW() or NOW()?
Hi All, I have a Student table with 5 columns. I have a typical issue where I need to show the datetime for student AttendanceDate for region Armonk and region London . I want any user from USA should only see the records of today's attendance date but for USA [Armonk]it is showing yesterday's date entries [marked in red in the below screenshot]and but for any user in Europe are able see the attendance of today's date . Could anyone suggest any DAX to handle this? I tried the below DAX but it didnt show any record for USA while it showed correct record for Europe region.I am located in Europe region and I published the report from my local machine? Does that make a difference? DAX calculated column formula :- SYSTEM_TIME_REF = NOW() FLAG = IF(student[attendance_date] >= student[SYSTEM_TIME_REF],0,1) Input data source : AttendanceDate Location StudentID Name Region 20.08.2021 11:05AM Gate1 101 Tom London 20.08.2021 10.03.22 Gate1 102 Hary London 20.08.2021 10.09.21 Gate2 103 Rohan Armonk 20.08.2021 09.03.18 Gate2 105 Mohan Armonk 19.08.2021 10.08.22 Gate2 109 Sam Armonk 19.08.2021 10.03.12 Gate2 110 Sally Armonk Expected Output: Whenever the Attendance_Date >= systemtime date then it should set the flag as 0 else 1.Therefore we can filter and show records of the present date based on system date but in case of USA it is showing one day older than the system date while in Europe region it is showing correct records.How to fix this issue , please suggest a DAX? Can someone please suggest any DAX changes or any DAX forumla to handle this in a better way and to rectify this issue? Kind regards SameerAverage Last 6 months accumulated
Hello dear community, I come to you so that you can help me with this DAX calculation. I need a Calculated Column to show me: the average of the last 6 months accumulated for each collaborator (in case the collaborator appears> = 6 times in the year); or in the event that the employee in the current year period appears less than 6 months, showing the average based on the number of months that the employee has been in the company in the current period, for example: Year Filter: 2021 Current month: May2021 (5) Seniority of the collaborator to May2021: 3 months (that is, the collaborator appears 3 times until May, if in June it becomes active, the collaborator when we are in June then it will appear 4 times, and so on) So the average will be = (Mar + Apr + May) / 3 Also consider that I already have a calendar table in my Power BI. Here I have an example sample of what I want exactly (column "Average Last 6 months), you can download the file here: I also leave the extract here: I read them, I hope they can help me as soon as possible. and thank you very much in advance. Nestor ReyesWANTED! DAX MINDMAP is looking for support
In order to get a bit of an order in the jungle of DAX functions, I was looking for a mindmap, which I found earlier for SQL and pretty helpful. Surprisingly, I couldn't find one ... or let's say, Google did not find one. So, what shall I do? I created it myself and here it is. A DAX mindmap. I partly followed the MS documentation and what I know about DAX, which is a bit more than rudimentary. The mindmap does not contain all DAX functions, then the sheet would probably have the size of a soccer field ... or baseball ... whatever is your favorite game. Some functions appear double as they fit in more than one category. Each function has a link to the related MS doc page for reference. It would be great to get you involved. Everyone is welcome to participate on the creation, just let me now, I add you to the board team (hope I figure out how to do that). After finalizing we will share it to the whole community. https://miro.com/app/board/o9J_ldNTpdw=/
