Forum Discussion
Need help formulating a DAX expression or something for count occurrences in rows
Ok, so let's say Column A is the index column with unique ID values for all other columns.
I need to find out how to count the times the specific ID has an occurrance of a number specifically in a row. So if we take ID 1, and go through the entire row 2 B -> 2 E, I need it to return the number 2, as 20 occurs twice. I do not want it to count the number of times 0 occurs, and there will be no unique values per row. (The number instead of 20, could be 1 or 2 or 3 whatever and could change per column)
Hopefully this makes sense! Looking forward to any help 🙂
BIHelp
Hi, Anonymous
According to your description, I think the best and easy way is to unpivot the column first.
Like this:
Then create a measure to show the count you want.
Like this:
Measure = COUNTROWS ( FILTER ( ALL ( 'Table' ), [ID] = SELECTEDVALUE ( 'Table'[ID] ) && [Value] > 0 ) )If you want to filter A or B, you can try this:
Measure = COUNTROWS ( FILTER ( ALL ( 'Table' ), [ID] = SELECTEDVALUE ( 'Table'[ID] ) && [Value] > 0 && SEARCH ( "A", [Attribute],, -1 ) > 0 ) )Below is my sample.
Did I answer your question ? Please mark my reply as solution. Thank you very much.If not, please feel free to ask me.Best Regards,Community Support Team _ Janey
5 Replies
- VahidDM
Super User
Hi Anonymous
It's not clear for me, how did you select ID 1 and in row 2 from 2B to 2E you found 20 as it occurs twice?
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/Hopefully this helps to clarify, so here, in the first picture I have attached a picture of the fields & table.
Here is a picture of my measurement, which it is almost doing correctly, but I need it to not count the 0.
This is a picture of the values inside of the actual table. So from A1-A4 basically, I just want it to count instances of a number being above 0, but I can't seem to figure it out. I do need it to say 0 in general but I just don't need it to show when calculating the column by column row counts.
- VahidDM
Hi Anonymous
Try this measure:
Measure = VAR _A = FILTER( UNION( VALUES( 'Table'[A1] ), VALUES( 'Table'[A2] ), VALUES( 'Table'[A3] ), VALUES( 'Table'[A4] ) ), [A1] <> 0 ) RETURN COUNTROWS( _A )Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
- Ashish_Mathur
Hi,
Using the Query Editor, select the first column and select "Unpivot other columns". To your slicer visual, drag ID and select any one ID. To your Table/matrix visual, drag the Attribute field and write this measure
Measure = countrows(Data)
Filter the visual on the ID with numbers > 0.
Hope this helps.
- v-janeyg-msft
Community Support
Hi, Anonymous
According to your description, I think the best and easy way is to unpivot the column first.
Like this:
Then create a measure to show the count you want.
Like this:
Measure = COUNTROWS ( FILTER ( ALL ( 'Table' ), [ID] = SELECTEDVALUE ( 'Table'[ID] ) && [Value] > 0 ) )If you want to filter A or B, you can try this:
Measure = COUNTROWS ( FILTER ( ALL ( 'Table' ), [ID] = SELECTEDVALUE ( 'Table'[ID] ) && [Value] > 0 && SEARCH ( "A", [Attribute],, -1 ) > 0 ) )Below is my sample.
Did I answer your question ? Please mark my reply as solution. Thank you very much.If not, please feel free to ask me.Best Regards,Community Support Team _ Janey
