Forum Discussion
count multiple from multiple columns into one
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...
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]
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.
7 Replies
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]
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.
thanks
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
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.
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
