topic Re: Count differents values with condition in DAX Commands and Tips

Count differents values with condition

Anonymous — Fri, 04 Aug 2023 16:00:58 GMT

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.

Re: Count differents values with condition

Anonymous — Fri, 04 Aug 2023 16:14:14 GMT

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

Re: Count differents values with condition

Anonymous — Fri, 04 Aug 2023 16:37:53 GMT

Hello Anonymous

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

Re: Count differents values with condition

Anonymous — Mon, 07 Aug 2023 07:50:22 GMT

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

Re: Count differents values with condition

Anonymous — Tue, 08 Aug 2023 18:40:03 GMT

Anonymous

Thanks a lot.