Count differents values with condition

[Anonymous]

Hi, I need a help to create a colunm in DAX.

The colunm "How many different hour" need to count how many different hours I have per "Type", for example: 

Type A have 2 different hours (20:20:53 and 22:15:41) so the colunm "How many different hour" bring me 2.

Type B have 3 different hours (19:45:22, 08:12:00 and 12:34:56) so the colunm "How many different hour" bring me 3. 

ROW	Hour	Type	How many different hour
1	20:20:53	A	2
2	20:20:53	A	2
3	20:20:53	A	2
4	22:15:41	A	2
5	22:15:41	A	2
6	22:15:41	A	2
7	22:15:41	A	2
8	22:15:41	A	2
9	22:15:41	A	2
10	19:45:22	B	3
11	19:45:22	B	3
12	08:12:00	B	3
13	08:12:00	B	3
14	08:12:00	B	3
15	12:34:56	B	3
16	12:34:56	B	3
17	12:34:56	B	3

 Anybody can help me? 

Thanks a lot.

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Flag = 
var _table1=
SUMMARIZE('Table','Table'[Type],'Table'[Hour])
return
COUNTX(
    FILTER(_table1,
    [Type]=EARLIER('Table'[Type])),[Hour])

2. Result:

Best Regards,

Liu Yang

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

[Anonymous]

@Anonymous 

Thanks a lot.

[HassanAshas]

Not sure if this is the most efficient solution but try this as calculated column, 

CalculatedColumn = 

var _Value = 'Table'[Type]

var _Table = 
    FILTER(
        'Table', 
        'Table'[Type] = _Value
    )

var _Result = 
    CALCULATE(
        DISTINCTCOUNT('Table'[Hour]), 
        _Table
    )

return _Result

[Anonymous]

Hello @HassanAshas 

Doesn't work, appears this message: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.