Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more

Reply
Inácio
Frequent Visitor

help COUNTX

I need the Total to give me how much  "1"  there is on line
Incio_0-1722521532973.png

 

3 ACCEPTED SOLUTIONS
Shravan133
Solution Sage
Solution Sage

To create a measure that counts the occurrences of "1" in each row across multiple columns, you can use DAX. This involves creating a calculated column or measure that iterates over each row and sums up the values.

Assuming you have a table named YourTable with columns Column1, Column2, Column3, ..., ColumnN, here's how you can achieve this:

Creating a Calculated Column

If you want to create a calculated column to store the count of "1"s for each row, follow these steps:

  1. Go to the Modeling tab in Power BI Desktop.
  2. Click on New Column.
  3. Enter the following DAX formula:

 

CountOfOnes =

VAR RowData = { YourTable[Column1], YourTable[Column2], YourTable[Column3], ... , YourTable[ColumnN] }

RETURN COUNTX(

FILTER(

 RowData,

 [Value] = 1 ),

 [Value] )

Replace Column1, Column2, ..., ColumnN with the actual column names of your table.

Creating a Measure

If you prefer to create a measure that calculates the count of "1"s dynamically, follow these steps:

  1. Go to the Modeling tab in Power BI Desktop.
  2. Click on New Measure.
  3. Enter the following DAX formula:

 

TotalCountOfOnes =

SUMX( YourTable,

VAR RowData = { YourTable[Column1],

 YourTable[Column2], YourTable[Column3], ... , YourTable[ColumnN] } RETURN

COUNTX( FILTER( RowData, [Value] = 1 ),

 [Value] ) )

Replace Column1, Column2, ..., ColumnN with the actual column names of your table.

 

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

You havenot shared much information so my measure is pure guesswork.  Try this measure

Measure = countrows(filter(values(Hora[Hora]),[measure1])

Measure1 is the measure you have written which returns 1's.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

v-jtian-msft
Community Support
Community Support

Hello,@Ashish_Mathur and @Shravan133 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,@Inácio .I am glad to help you.
Here is my test:

vjtianmsft_0-1722851235429.png
Correct Measure:

Measure_result = 
COUNTX(
    SUMMARIZECOLUMNS(
        'Table'[Type],
        'Table'[Date]
    )
    ,[Measure 2])
    //change the [measure 2] to your own measure

vjtianmsft_1-1722851291649.png

 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

View solution in original post

3 REPLIES 3
v-jtian-msft
Community Support
Community Support

Hello,@Ashish_Mathur and @Shravan133 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,@Inácio .I am glad to help you.
Here is my test:

vjtianmsft_0-1722851235429.png
Correct Measure:

Measure_result = 
COUNTX(
    SUMMARIZECOLUMNS(
        'Table'[Type],
        'Table'[Date]
    )
    ,[Measure 2])
    //change the [measure 2] to your own measure

vjtianmsft_1-1722851291649.png

 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

Ashish_Mathur
Super User
Super User

Hi,

You havenot shared much information so my measure is pure guesswork.  Try this measure

Measure = countrows(filter(values(Hora[Hora]),[measure1])

Measure1 is the measure you have written which returns 1's.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Shravan133
Solution Sage
Solution Sage

To create a measure that counts the occurrences of "1" in each row across multiple columns, you can use DAX. This involves creating a calculated column or measure that iterates over each row and sums up the values.

Assuming you have a table named YourTable with columns Column1, Column2, Column3, ..., ColumnN, here's how you can achieve this:

Creating a Calculated Column

If you want to create a calculated column to store the count of "1"s for each row, follow these steps:

  1. Go to the Modeling tab in Power BI Desktop.
  2. Click on New Column.
  3. Enter the following DAX formula:

 

CountOfOnes =

VAR RowData = { YourTable[Column1], YourTable[Column2], YourTable[Column3], ... , YourTable[ColumnN] }

RETURN COUNTX(

FILTER(

 RowData,

 [Value] = 1 ),

 [Value] )

Replace Column1, Column2, ..., ColumnN with the actual column names of your table.

Creating a Measure

If you prefer to create a measure that calculates the count of "1"s dynamically, follow these steps:

  1. Go to the Modeling tab in Power BI Desktop.
  2. Click on New Measure.
  3. Enter the following DAX formula:

 

TotalCountOfOnes =

SUMX( YourTable,

VAR RowData = { YourTable[Column1],

 YourTable[Column2], YourTable[Column3], ... , YourTable[ColumnN] } RETURN

COUNTX( FILTER( RowData, [Value] = 1 ),

 [Value] ) )

Replace Column1, Column2, ..., ColumnN with the actual column names of your table.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.