loop through columns and add to counter

[Vickram]

I have a table which contains multiple indicator columns and corresponding benchmark columns for each indicator. For example: each row in the table has ColA, BenchamarkA, ColB, BenchmarkB, ColC, BenchmarkC, ColD, BenchmarkD etc. I have all together 15 indicators and 15 benchmark values. I need to find a record which contains at least 3 or more indicator below benchmark values. Not sure how to achieve this.

is it possible to iterate through each column and comparing it with its benchmark value? If it is possible then I was thinking maybe we can create a measure whose value will be incremented by one if indicator found to be less than benchmark value while looping through the columns.

I am fairly new to Power BI, so need some assistance. Any help will be greatly appreciated.

[amitchandak]

@Vickram , Not very clear.

You have to create formula like this in Dax

If( [ColC]> [Benchmark C],1,0)

[Vickram]

Sorry, my question was not much clear.

I am thinking of creating a new column. To calculate the value of this column, i need to to compare each of the 15 indicator column values to their corresponding benchmark values, and if any 3 of them are below their corresponding benchmark values, then I will update the new column values as "Failed", otherwise "Passed". As you can see, to flag it as "Failed", i need to compare all 15 indicator columns and count how many of them are below benchmark.

 If this is not clear enough, i will attach a sample data. Thanks in advance.

[v-lili6-msft]

hi @Vickram 

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[Vickram]

Hi @ links,

Sample data is as below. "New column" in the table below is the result column I am expecting. Each ColA....ColH needs to be compared against corresponding BenchA...BenchH values. If any 3 Col values are less than Bench values then the company will be considered Fail, otherwise Pass. In below sample C1003 failed in 4 criteria and C1007 failed in 3, so they are tagged as "Fail" and rest as "Pass" in the New Column. Hope it makes sense.

CompanyNo	ColA	BenchA	ColB	BenchB	ColC	BenchC	ColD	BenchD	ColE	BenchE	ColF	BenchF	ColG	BenchG	colH	BenchH	New Column
C1000	100	90	78	70	98	95	48	60	60	55	49	55	99	80	45	30	Pass
C1001	97	90	65	70	110	95	55	60	78	55	88	55	94	80	52	30	Pass
C1003	89	90	72	70	94	95	65	60	52	55	65	55	78	80	40	30	Fail
C1004	99	90	75	70	98	95	62	60	65	55	58	55	89	80	35	30	Pass
C1005	74	90	87	70	101	95	72	60	55	55	56	55	82	80	42	30	Pass
C1006	92	90	72	70	97	95	62	60	57	55	57	55	87	80	37	30	Pass
C1007	85	90	78	70	95	95	59	60	70	55	63	55	83	80	28	30	Fail

[v-lili6-msft · Accepted solution]

HI  @Vickram 

Sorry for the late reply, 

for your data structure, you need to create a column as below:

Result = IF(
IF('Table'[ColA]<'Table'[BenchA],1,0)+
IF('Table'[ColB]<'Table'[BenchB],1,0)+
IF('Table'[ColC]<'Table'[BenchC],1,0)+
IF('Table'[ColD]<'Table'[BenchD],1,0)+
IF('Table'[ColE]<'Table'[BenchE],1,0)+
IF('Table'[ColF]<'Table'[BenchF],1,0)+
IF('Table'[ColG]<'Table'[BenchG],1,0)+
IF('Table'[ColH]<'Table'[BenchH],1,0)
>=3,"Fail","Pass")

 Result:

Regards,

Lin

[Vickram]

Thanks @Lin.

It does work.

As a workaround, I previosuly calculated the score for each benchmark, then finally use that score to determine "Pass" or "Fail". There were some NULLs (which became Blank after importing to Power BI), which I needed to use in my score calculation. I excluded the NULLs from the  calculation as they are "data not available". I will re-write the IF condidtion you provided to make sure that it excludes NULL from the comparision. 

Thanks again for your response.