Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Farandolai
New Member

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

}

 

2 ACCEPTED SOLUTIONS
ValtteriN
Super User
Super User

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)

ValtteriN_0-1713021499973.png

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:

ValtteriN_1-1713022688682.png

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/





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

Proud to be a Super User!




View solution in original post

tamerj1
Super User
Super User

Hi @Farandolai 

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] )
)
)
)

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @Farandolai 

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
Super User
Super User

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)

ValtteriN_0-1713021499973.png

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:

ValtteriN_1-1713022688682.png

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/





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

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.