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

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.

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

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