Forum Discussion
Count value based on condition and other sheet with DAX
Dear friend
Please suggested me how can i create Count break column in table 2 that count break individual by day from table 1
Table 1
| Date | Name | Break |
| 01-01-20 | Jim | |
| 01-01-20 | Jim | Break |
| 01-01-20 | Jim | |
| 02-02-20 | Jim | |
| 02-02-20 | Jim | Break |
| 02-02-20 | Jim | |
| 01-01-20 | Max | |
| 01-01-20 | Max | Break |
| 01-01-20 | Max | |
| 02-02-20 | Max | Break |
| 02-02-20 | Max |
Table 2
| Date | Name | Count break |
| 01-01-20 | Jim | 1 |
| 02-02-20 | Jim | 1 |
| 01-01-20 | Max | 1 |
| 02-02-20 | Max | 1 |
Can any one please suggest me.
Thanks
Anonymous , Try a new column like
countx(filter(Table1,Table1[Name] =Table2[Name] && Table1[Date] =Table2[Date] && not(isblank(Table1[Break]))),Table1[Name] )
8 Replies
- Sujit_Thakur
Solution Sage
Dear friend Anonymous
First relate tables with name 👍
Then make this measure
Count_break = Countrows (Filter (table1, 'table1'[Break] = "Break"))
Use this measure in a matrix of table it will give you answer
If any doubt or any error please let me know I'll be happy to help
Please give a kudos aNd if this post solves your problem please accept it as solution
Regards ,
Sujit Thakur
Thank for your advice Sujit_Thakur but could it be only DAX with not relationship?
- Sujit_Thakur
Yes it can be done. , Please use and see it works
If it doesn't please let me know
Technically speaking you don't actually need a second table.
Just throw this into a measureCountBreak = CALCULATE ( COUNTA ( 'Table'[Break] ), 'Table'[Break] IN { "Break" } )Then add Name, Data and the new measure to a table
However, if you do need to use table 2. Ensure they have a relationship on the Date column and it should work.
- amitchandak
Super User
Anonymous , Try a new column like
countx(filter(Table1,Table1[Name] =Table2[Name] && Table1[Date] =Table2[Date] && not(isblank(Table1[Break]))),Table1[Name] )
amitchandak i cant type table 2 in formula because it isnt manage relationship
amitchandak Thank you so much!!!
