Forum Discussion

XaviOV

Helper V

6 years ago

Solved

Counting ditinte values in a row

Hello

I want to know the amount of a value ("0", "na"...) in an entire row.

For example I have several extinguishers (columns) and in each row different values (extinguisher parameters).

Thank you

Xavi

v-alq-msft

6 years ago

Hi, XaviOV

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create three measures as below.

Count 0 = 
var _table = 
SUMMARIZE(
    'Table',
    'Table'[Pregunta],
    "Re",
    var _e11=SELECTEDVALUE('Table'[Extintor 1.1])
    var _e12=SELECTEDVALUE('Table'[Extintor 1.2])
    var _e13=SELECTEDVALUE('Table'[Extintor 1.3])
    var _e14=SELECTEDVALUE('Table'[Extintor 1.4])
    var _e15=SELECTEDVALUE('Table'[Extintor 1.5])
    var tab = {_e11,_e12,_e13,_e14,_e15}
    var _result = 
    COUNTROWS(
        FILTER(
            tab,
            [Value]="0"
        )
    )
    return
    IF(
        ISBLANK(_result),
        0,
        _result
    )
)
return
SUMX(
    _table,
    [Re]
)

Count 1 = 
var _table = 
SUMMARIZE(
    'Table',
    'Table'[Pregunta],
    "Re",
    var _e11=SELECTEDVALUE('Table'[Extintor 1.1])
    var _e12=SELECTEDVALUE('Table'[Extintor 1.2])
    var _e13=SELECTEDVALUE('Table'[Extintor 1.3])
    var _e14=SELECTEDVALUE('Table'[Extintor 1.4])
    var _e15=SELECTEDVALUE('Table'[Extintor 1.5])
    var tab = {_e11,_e12,_e13,_e14,_e15}
    var _result = 
    COUNTROWS(
        FILTER(
            tab,
            [Value]="1"
        )
    )
    return
    IF(
        ISBLANK(_result),
        0,
        _result
    )
)
return
SUMX(
    _table,
    [Re]
)

Count na = 
var _table = 
SUMMARIZE(
    'Table',
    'Table'[Pregunta],
    "Re",
    var _e11=SELECTEDVALUE('Table'[Extintor 1.1])
    var _e12=SELECTEDVALUE('Table'[Extintor 1.2])
    var _e13=SELECTEDVALUE('Table'[Extintor 1.3])
    var _e14=SELECTEDVALUE('Table'[Extintor 1.4])
    var _e15=SELECTEDVALUE('Table'[Extintor 1.5])
    var tab = {_e11,_e12,_e13,_e14,_e15}
    var _result = 
    COUNTROWS(
        FILTER(
            tab,
            [Value]="na"
        )
    )
    return
    IF(
        ISBLANK(_result),
        0,
        _result
    )
)
return
SUMX(
    _table,
    [Re]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Counting ditinte values in a row.pbix34 KB

6 Replies

Mariusz
Community Champion
6 years ago
Hi XaviOV

You can use DAX Column, as below.

count = VAR __valuesToCount = { "0", "na" } VAR __rowValues = { 'Table'[col1], 'Table'[col2], 'Table'[col3] } RETURN SUMX( __rowValues, INT( [Value] IN __valuesToCount ) )

this will give you result as below.

or use Power Query and add custom column like:

= Table.AddColumn(Source, "Custom", each List.Count( List.Intersect( { Record.ToList( _ ), { "0", "na" } } ) ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn
Fowmy
Super User
6 years ago
XaviOV

Can you explain further?

What do what as a result ? is it the combination of all or only selected values?
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
YouTube, LinkedIn
- XaviOV
  Helper V
  6 years ago
  Hello
  
  For example from this table:
  
  Check "0" --> 4
  Check "1" --> 1
  Control "0" --> 4
  Control "na" --> 1
  Status "0" --> 5
  
  Best regards
  Xavi
  - Fowmy
    Super User
    6 years ago
    XaviOV
    
    You need to UnPivot your data in Power Query and then do the summarization in Power Query or using a visual in Power BI?
    Share data in excel or in a format that we can copy (not the picture).
    
    ________________________
    Did I answer your question? Mark this post as a solution, this will help others!.
    Click on the Thumbs-Up icon on the right if you like this reply 🙂
    YouTube, LinkedIn
amitchandak
Super User
6 years ago
XaviOV ,

If they are column, you have to create the sum of all and check

if( sum(Table[Col1])+sum(Table[Col2])+sum(Table[Col3])+sum(Table[Col4 ]) =0,1,0)

or calculate([measure], filter(Table,if( sum(Table[Col1])+sum(Table[Col2])+sum(Table[Col3])+sum(Table[Col4 ]) =0)