Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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...
Solved! Go to 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]
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.
Is this possible to do with creating a new column instead of measure ??
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |