Reproduce FOREACH() function in DAX Power BI

[nuttybuddy]

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

[nuttybuddy · Accepted solution]

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

[v-yuezhe-msft · Accepted solution]

@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.



Regards,
Lydia

[Greg_Deckler]

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.

[nuttybuddy]

Does SUMMARIZE not create a table?

I just want a calculated column

[Greg_Deckler]

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.

[nuttybuddy · Accepted solution]

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

[v-yuezhe-msft · Accepted solution]

@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.



Regards,
Lydia

[nuttybuddy]

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.

[v-yuezhe-msft · Accepted solution]

@nuttybuddy,

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

Regards,
Lydia

[Phil_Seamark]

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