count multiple from multiple columns into one

[bhagyaraj]

Hi,

I am trying to count multilple column values into one column.

I have 4 column values BMI,BP,sugar,Lipid respective values normal and abnormal below screen shot for ref.

Kindly sugguest me how to get the count number(Dax Formula) where ever Abnormal exists in 4 columns.

Expectation highlighted in color in below screen shot.

Thanks...

[TomMartens · Accepted solution]

Hey,

I'm not sure if this is exactly what you are looking for, the result of the measure looks promising

Here is the measure

Measure = 
SUMX(
UNION(
	SELECTCOLUMNS('Table1',"ContainsA", 'Table1'[Dim1]),
	SELECTCOLUMNS('Table1',"ContainsA", 'Table1'[Dim2]),
	SELECTCOLUMNS('Table1',"ContainsA", 'Table1'[Dim3]),
	SELECTCOLUMNS('Table1',"ContainsA", 'Table1'[Dim4])			
)
,IF([ContainsA] = "A", 1, 0))

Basically this measure creates a table for each row (maybe you have to put CALCULATE() to its magically use if your table and model much more complex than my simple model.

Than SUMX iterates over the 4 ROWS and sums the result.

I created a measure but this should also be usable by creating a calculated column.

Cheers

[note to myself:  Compare Multiple Columns]

[Anonymous]

Is this possible to do with creating a new column instead of measure ??

[TomMartens · Accepted solution]

Hey,

I'm not sure if this is exactly what you are looking for, the result of the measure looks promising

Here is the measure

Measure = 
SUMX(
UNION(
	SELECTCOLUMNS('Table1',"ContainsA", 'Table1'[Dim1]),
	SELECTCOLUMNS('Table1',"ContainsA", 'Table1'[Dim2]),
	SELECTCOLUMNS('Table1',"ContainsA", 'Table1'[Dim3]),
	SELECTCOLUMNS('Table1',"ContainsA", 'Table1'[Dim4])			
)
,IF([ContainsA] = "A", 1, 0))

Basically this measure creates a table for each row (maybe you have to put CALCULATE() to its magically use if your table and model much more complex than my simple model.

Than SUMX iterates over the 4 ROWS and sums the result.

I created a measure but this should also be usable by creating a calculated column.

Cheers

[note to myself:  Compare Multiple Columns]

[bhagyaraj · Accepted solution]

Hey,

I got other measure formula

Here is the measure

Measure2 = 
            IF(Table1[DIM1]="A",1,0)+
            IF(Table1[DIM2]="A",1,0)+
            IF(Table1[DIM3]="A",1,0)+
            IF(Table1[DIM4]="A",1,0)

Thanks for you help.

[bhagyaraj]

Hello Mr.Tom,

Thanks for query and its works for few records and rest of records shows more then having columns.

For example the formula applied for  7 columns where as the result  shows the value more than 7.

Kindly provide any another solution.

[TomMartens]

Hey,

to be of further help you have to provide sampledata, that help to reproduce the wrong beings of my solution.

Prepare Excel Data or PBIX using Enter Data and make the file available from onedrive or dropbox.

Cheers

[bhagyaraj]

thanks

[TomMartens]

Hey,

it will help others if you flag my answer as solution and if you like my answer do not hesitate to provide a kudo.

Cheers