Forum Discussion

nuttybuddy's avatar
nuttybuddy
Frequent Visitor
8 years ago
Solved

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

Need Help
  • nuttybuddy's avatar
    nuttybuddy
    8 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

  • Anonymous's avatar
    Anonymous
    8 years ago

    nuttybuddy,

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

    Regards,
    Lydia

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    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's avatar
      nuttybuddy
      Frequent Visitor
      8 years ago

      Does SUMMARIZE not create a table?

       

      I just want a calculated column

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        8 years ago

        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.