Conditionally removing duplicates and then finding average

Hi bossamus 

 

Depending on the calcultion you want to do you may use a syntax similar to this one:

Number of teams by certification = 
VAR Temp_table =
    SUMMARIZE (
        Certification,
        Certification[Team],
        Certification[Certification Level],
        "N_Member", SUM ( Certification[Number of members certified] ),
        "Rows_Total",
            COUNTROWS (
                FILTER (
                    ALL ( Certification ),
                    Certification[Team] = SELECTEDVALUE ( certification[team] )
                )
            )
    )
RETURN
    COUNTROWS (
        FILTER (
            Temp_table,
             ( [Rows_Total] = 1 )
                || ( [N_Member] <> 0
                && [Rows_Total] > 1 )
        )
    )

 

The trick here is the filter part of the syntax,

 

If you had the following syntax you get the teams names:

Teams names = 
VAR Temp_table =
    SUMMARIZE (
        Certification,
        Certification[Team],
        Certification[Certification Level],
        "N_Member", SUM ( Certification[Number of members certified] ),
        "Rows_Total",
            COUNTROWS (
                FILTER (
                    ALL ( Certification ),
                    Certification[Team] = SELECTEDVALUE ( certification[team] )
                )
            )
    )
RETURN
    CONCATENATEX(
        FILTER (
            Temp_table,
             ( [Rows_Total] = 1 )
                || ( [N_Member] <> 0
                && [Rows_Total] > 1 )
        )
    , Certification[Team], ",")

 

I assume that you don't want to filter out the information from your main table.

 

If you want to do the calculation based on cleaning the data on the query editor you can:

• Group Columns by team
• Merge the groups with the certification in order to get total rows
• Add a custom column if the following syntax:

if [Grouped Rows.Count] = 1  then 1 else if [Number of members certified]  >  0 then 1 else 0

• filter out the 0 from the table

Full code below and in attach PBIX file:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7ITEktKlbSUfLLV3BOLSrJTMtMTizJzM8DChkoxeogKzEEYmM0MSMgNgeL+QA14TUoJDOdgFVwFYZgk2NjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, #"Certification Level" = _t, #"Number of members certified" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"Certification Level", type text}, {"Number of members certified", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Team"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Team"}, #"Grouped Rows", {"Team"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Grouped Rows.Count"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "Custom", each if [Grouped Rows.Count] = 1  then 1 else if [Number of members certified]  >  0 then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
    #"Filtered Rows"

This way you don't need to use any dax.