Reply
Regular Visitor
Posts: 16
Registered: ‎02-15-2018
Accepted Solution

Reproduce FOREACH() function in DAX Power BI

Hi all

There is a specific function in SAP Web intelligence which I am trying to reproduce in Power BI

 

This FOREACH() function forces the aggregated dimension based on the dimention you want to group on

Here is the syntax for the function as below...

 

FTE Size = If([FTE]=0;"0";
If([FTE] <=29;"1-29";
If([FTE] <=49;"30-49";
If([FTE] <=99;"50-99";">=100")))) ForEach ([Department])

 

 

Thanks for your help in advance.

 

 

Cheers


Accepted Solutions
Regular Visitor
Posts: 16
Registered: ‎02-15-2018

Re: Reproduce FOREACH() function in DAX Power BI

Here is the sample data...

 

Department   sub department  FTE

ABV               1E                    12

ABV               1S                    13

DDD               2A                   50

DDD               2B                   70

BGR                3E                   34

BGR                3T                   90

            

 

Expected result

FTE          Department-count

0-20         1

21-30       0

31-40       1

41-50       1

>50          2

 

View solution in original post

Moderator
Posts: 10,201
Registered: ‎03-10-2016

Re: Reproduce FOREACH() function in DAX Power BI

@nuttybuddy,

Create a column using DAX below in your table.

FTE size = If([FTE]=0;"0";
If([FTE] <=20;"0-20";
If([FTE] <=30;"21-30";
If([FTE] <=40;"31-40";if([FTE]<=50;"41-50";">=50")))))


Create a table visual as follows, please choose "Count" aggregation for Deparement field.

1.JPG2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Moderator
Posts: 10,201
Registered: ‎03-10-2016

Re: Reproduce FOREACH() function in DAX Power BI

@nuttybuddy,

Right click your table and select "New column", then apply my DAX.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Super User
Posts: 10,753
Registered: ‎07-11-2015

Re: Reproduce FOREACH() function in DAX Power BI

My first impression of this is that you would create your Measure with your IF statement and then put it in a visual along with Department. If you want it all done in a single measure, you would use SUMMARIZE.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Regular Visitor
Posts: 16
Registered: ‎02-15-2018

Re: Reproduce FOREACH() function in DAX Power BI

Does SUMMARIZE not create a table?

 

I just want a calculated column

Super User
Posts: 10,753
Registered: ‎07-11-2015

Re: Reproduce FOREACH() function in DAX Power BI

I'd need to see your data or an example representation of your data and the output you are trying to achieve. Otherwise, we'll just spin our wheels on this.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Posts: 2,618
Registered: ‎11-29-2015

Re: Reproduce FOREACH() function in DAX Power BI

Looks like a combination of SWITCH and one of the iterator functions would do the trick.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Regular Visitor
Posts: 16
Registered: ‎02-15-2018

Re: Reproduce FOREACH() function in DAX Power BI

Here is the sample data...

 

Department   sub department  FTE

ABV               1E                    12

ABV               1S                    13

DDD               2A                   50

DDD               2B                   70

BGR                3E                   34

BGR                3T                   90

            

 

Expected result

FTE          Department-count

0-20         1

21-30       0

31-40       1

41-50       1

>50          2

 

Moderator
Posts: 10,201
Registered: ‎03-10-2016

Re: Reproduce FOREACH() function in DAX Power BI

@nuttybuddy,

Create a column using DAX below in your table.

FTE size = If([FTE]=0;"0";
If([FTE] <=20;"0-20";
If([FTE] <=30;"21-30";
If([FTE] <=40;"31-40";if([FTE]<=50;"41-50";">=50")))))


Create a table visual as follows, please choose "Count" aggregation for Deparement field.

1.JPG2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor
Posts: 16
Registered: ‎02-15-2018

Re: Reproduce FOREACH() function in DAX Power BI

Hi sorry

Got this error below....

 

A single value for column 'FTE' in table 'Table 1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Moderator
Posts: 10,201
Registered: ‎03-10-2016

Re: Reproduce FOREACH() function in DAX Power BI

@nuttybuddy,

Right click your table and select "New column", then apply my DAX.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.