COUNTROWS in table use other table values

[knasveschuk]

I would like to count rows in table2 using table1 for conditional filtering

Table1 is calculated: typesTime = DISTINCT('timeStatus'[type])

table1

typesTime

type

overtime

sick_pay

sick_no_pay

holiday

personal

military

jury

bereavement

normal

table2

TimeStatus
date	type	name
6/20/2020	overtime	fred
6/19/2020	sick_pay	john
6/20/2020	normal	mary
6/20/2020	normal	mary
6/20/2020	normal	fred
6/20/2020	normal	john
6/20/2020	normal	john

I would like to count number of each "type" in table2 using table1 to get desired output below, is this possible with DAX?

desired output

overtime	60
sick_pay	345
sick_no_pay	3
holiday	512
personal	201
military	17
jury	45
bereavement	245
normal	45620

[knasveschuk · Accepted solution]

I found another way around this I did not know it existed. I'm new to BI.

[v-lili6-msft]

hi  @knasveschuk 

For your case, the simple way is use this formula to create a table1 instead of yours

Table 1 = SUMMARIZE(TimeStatus,TimeStatus[type],"amount",CALCULATE(COUNTROWS(TimeStatus)))

another way is create a relationship between table 1 and table2, then in table 1 create a new column as this

Amount = CALCULATE(COUNTROWS('TimeStatus'))

and here is sample pbix file, please try it.

Regards,

Lin

[amitchandak]

@knasveschuk , not very clear to me . see if this can help

https://docs.microsoft.com/en-us/dax/treatas-function

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

[knasveschuk]

---

@amitchandak wrote:

@knasveschuk, not very clear to me . see if this can help

https://docs.microsoft.com/en-us/dax/treatas-function

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

---

I think I can do this another way, being new to BI I stumbled upon a solution without calculating anything