Multiple Occurrences

MintuBaruah 

Add a column and use it on a visual 

Freq = 

var __v = Table3[Column1] 
var __d = CALCULATE(COUNT(Table3[Column1]), Table3[Column1] = __v)
    
return
SWITCH(
    TRUE(),
    __d > 2 , "High",
    __d = 2 , "Normal",
    __d < 2 , "Low"
)

 

Hi MintuBaruah ,

 

I did it in two ways, please check.

 

• Use DAX:

 

Frequency(DAX) =
VAR _count =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Column1] ) )
RETURN
    SWITCH ( TRUE (), _count = 1, "Low", _count = 2, "Normal", _count >= 3, "High" )

 

 

 

• In Power Query:

1.Group by

2. Add a Conditional Column:

Here is the whole M syntax:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRWitXBoEwhlAWYgoqZgCkjDBKkKhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Expand", each _, type table [Column1=nullable number]}}),
    #"Expanded Expand" = Table.ExpandTableColumn(#"Grouped Rows", "Expand", {"Column1"}, {"Column1.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Expand",{"Column1.1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Frequency in PQ", each if [Count] = 1 then "Low" else if [Count] < 3 then "Normly" else "High")
in
    #"Added Custom"

 

 

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