Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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. 

 

ROWHourTypeHow many different hour
120:20:53A2
220:20:53A2
320:20:53A2
422:15:41A2
522:15:41A2
622:15:41A2
722:15:41A2
822:15:41A2
922:15:41A2
1019:45:22B3
1119:45:22B3
1208:12:00B3
1308:12:00B3
1408:12:00B3
1512:34:56B3
1612:34:56B3
1712:34:56B3

 

 Anybody can help me? 

 

Thanks a lot.

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello Anonymous 

       

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

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      Anonymous 

       

      Thanks a lot.