cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Count differents values with condition

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.

1 ACCEPTED SOLUTION
Community Support

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

4 REPLIES 4
Community Support

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

Helper III

Thanks a lot.

Helper V

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``````

Helper III

Hello @HassanAshas

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