Convert SAP BO measure with Foreach() operator to DAX formula

[MarcoT]

Hi I'm pretty new to SAP BO and I'm trying to convert a measure that I've found in a report to DAX language.

The foruma uses the context operator FOREACH() on two columns ([Data].[KPI Group] and Data].[KPI Name] ) and I don't know how to write it in DAX. Could you please help me figuring out?

Here's the code:

SUM( IF( [Not Available] FOREACH( [Data].[KPI Group];[Data].[KPI Name] )="Y" ) THEN 1 ELSE 0 )

Thanks

[Whitewater100]

Hi:

Here is an example, with no ashortcuts, so you can see how it works.

. First you have your table of KPI data. Then you can add a Calc Col "Has Name"

Then you create a measure to sum those with names.

Sum of KPI Name = SUM(Data[Has Name])

Here is a Card Visual:

I hope this helps!

[MarcoT]

Hi, thanks for your answer. I've understood the sum as count part, but I didn't understand where my [Not Available] column is in your example...

The 3 columns are in the same table, so you can consider the SAP BO measure like this:

SUM( IF( [Not Available] FOREACH( [KPI Group]; [KPI Name] )="Y" ) THEN 1 ELSE 0 )

What I didn't understand is the bold part. The count part is ok, but what does the foreach with two arguments do in this case?

I paste some data for example:

Not Available	KPI Name	KPI Group
	X	W
NO	X	W
YES	X	W
	Y	Z
NO	Y	Z
YES	Y	Z
	V	S
NO	V	S
YES	V	S

[Whitewater100]

Hi: To help me understand a bit more.

In your example, there is a KPI (X) and KPI Group(W). The top three rows. What is the answer you want for the first three rows?

0, 0, 1 ?

[MarcoT]

Hi, let me explain better. I don't know what the result should be, because I don't know SAP BO logi  behind that formula. I'm just trying to replicate it to PowerBI so that I can understand what it does. My doubt is on the foreach operator with two arguments as a parameter and how can I translate it in DAX language. Does it check if the Not Available field has "Y" value for alle the combination of KPI Name and KPI Group? I don't know. Do you know?

[Whitewater100]

Result 3 is grouping those two parameters then marking with a 1, if Not Available = YES.

That is how do to this in DAX. 

[Whitewater100]

Hi:

OK, I think this is what you mean. You want to group the KPI Name & Group and then see if available. Here is two ways for a result like that. Result 3 is what I'd say you want. Then you can sum these results in a measure like shown prior. I hope this solves this one.

Result 3 =

var na = 'Table'[Not Available]

return

CALCULATE(IF(na = "YES",1,0)

,ALLEXCEPT('Table','Table'[KPI Group],'Table'[KPI Name ]))

Result 2 is probably to simple based on what you say.

Result 2 =

var na = 'Table'[Not Available]

Return

CALCULATE(IF(na = "YES",1,0)

)

[MarcoT]

Hi, I tried your formula but [Not Available] is a table field, not a measure and I cannot assign it to a variable. I tried also to insert it in the IF condition but it cannot be accepted. Any other ideas?

[Whitewater100]

Hi Marco:

A variable can be either a table field (e.g. Result3  var kpiname = Table[KPI name]

or it can be a measure.

var sales = [Total Sales].  When you write a variable in a measure and you want to relate to a table field, then the variables can look like this:

VAR KPIName = SELECTEDVALUE(Table[KPI Name]

I have explained how to group KPI Group & KPI Name in the way you presented your problem.(the problem is shown as a single table with three fields). 

Are you able to mark this as solved? If you have a different scenario with different data that can be a separate question with new data.

Thank you..