Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to create an array and loop in DAX

I have a large amount of data with a lot of different work centers, but I want to calculate workcenter1(good parts/total parts) * workcenter2(good parts/total parts) * ... but only for a certain list of workcenter numbers. Is there a way to create the list of work centers in DAX or do I need to create a new column with M to create that list?

 

This is a mishmash of the code that is working and what I would like to do with it:

array BR41 = {"670205","670206","670302","670502","670902","670904","671002","671202"};
float RolledYield = 1;

for(int i=0;i<=7;i++)
{
string LoopedWorkcenterNumber = BR41[i];
float RolledYield = RolledYield *

// DAX code I have been using for calculating the filtered yield data
DIVIDE( CALCULATE( SUM(DWS_ConfPassAndScrap[PassQuantity]),FILTER(ALL(DWS_ConfPassAndScrap[Workcenternumber]),DWS_ConfPassAndScrap[Workcenter]= LoopedWorkcenterNumber)),
CALCULATE(SUM(DWS_ConfPassAndScrap[PassQuantity]),FILTER(ALL(DWS_ConfPassAndScrap[Workcenter]),DWS_ConfPassAndScrap[Workcenter]=LoopedWorkcenterNumber))
+CALCULATE(SUM(DWS_ConfPassAndScrap[ScrapQuantity]),FILTER(ALL(DWS_ConfPassAndScrap[Workcenter]),DWS_ConfPassAndScrap[Workcenter]=LoopedWorkcenterNumber)))

}

 

  • Hi,

    I would recommend using PRODUCTX function:

    test data:
    Here the goal is to have your logic applied for cost centers A,B and D. The result would be (1/2)*(2/2)*(1/2)

    Here is the dax:

    Measure 22 =

    var _workcenters = {"A","B","D"}

    RETURN
    PRODUCTX(
    ADDCOLUMNS(
    SUMMARIZE(FILTER('Table (31)','Table (31)'[Workcenter] in _workcenters),'Table (31)'[Workcenter]),"good/all",
    CALCULATE(COUNT('Table (31)'[ID]),'Table (31)'[Goodpart]=1)/
    CALCULATE(COUNT('Table (31)'[ID])))
    ,[good/all])


    End result:

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

  • Hi Anonymous 

    not sire if I fully understand the requirement however please try

    Measure1 =
    PRODUCTX (
    TREATAS (
    {
    "670205",
    "670206",
    "670302",
    "670502",
    "670902",
    "670904",
    "671002",
    "671202"
    },
    DWS_ConfPassAndScrap[Workcenter]
    ),
    CALCULATE (
    DIVIDE (
    SUM ( DWS_ConfPassAndScrap[PassQuantity] ),
    SUM ( DWS_ConfPassAndScrap[PassQuantity] )
    + SUM ( DWS_ConfPassAndScrap[ScrapQuantity] )
    )
    )
    )

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion

    Hi Anonymous 

    not sire if I fully understand the requirement however please try

    Measure1 =
    PRODUCTX (
    TREATAS (
    {
    "670205",
    "670206",
    "670302",
    "670502",
    "670902",
    "670904",
    "671002",
    "671202"
    },
    DWS_ConfPassAndScrap[Workcenter]
    ),
    CALCULATE (
    DIVIDE (
    SUM ( DWS_ConfPassAndScrap[PassQuantity] ),
    SUM ( DWS_ConfPassAndScrap[PassQuantity] )
    + SUM ( DWS_ConfPassAndScrap[ScrapQuantity] )
    )
    )
    )

  • ValtteriN's avatar
    ValtteriN
    Community Champion

    Hi,

    I would recommend using PRODUCTX function:

    test data:
    Here the goal is to have your logic applied for cost centers A,B and D. The result would be (1/2)*(2/2)*(1/2)

    Here is the dax:

    Measure 22 =

    var _workcenters = {"A","B","D"}

    RETURN
    PRODUCTX(
    ADDCOLUMNS(
    SUMMARIZE(FILTER('Table (31)','Table (31)'[Workcenter] in _workcenters),'Table (31)'[Workcenter]),"good/all",
    CALCULATE(COUNT('Table (31)'[ID]),'Table (31)'[Goodpart]=1)/
    CALCULATE(COUNT('Table (31)'[ID])))
    ,[good/all])


    End result:

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/