Forum Discussion

Jorine's avatar
Jorine
New Member
3 years ago
Solved

Distinct count with condition to get unique date only

I need the Dax language to get the distinct count of my tenant records. Below is the table, how can i get the distinct key like below? I only need to count in when the date is different and I have a long list of units. Been trying a few method but it doesnt return me the Distinct key i need below. 😞

 

Unit NumberDefect DateDistinct Key
Office1/1/20231
Office1/1/20230
Office1/1/20230
Office2/2/20231
Office3/3/20231


PBI 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Jorine ,

     

    You can then create a new blank query in the PowerQuery Editor, copy this code into the Advanced Editor, and refer to these steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k9Ly0xOVdJRMtQ31DcyMDJWitUhU9RI3wiLqLG+MVQ0FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unit Number" = _t, #"Defect Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit Number", type text}, {"Defect Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Unit Number", "Defect Date"}, {{"Data", each _},{"MinIndex", each List.Min(_[Index])}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Index"}, {"Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Distinct Key", each if [Index] = [MinIndex] then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "MinIndex"})
in
    #"Removed Columns"

    Query overview in Power BI Desktop - Power BI | Microsoft Learn

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Jorine ,

     

    You can then create a new blank query in the PowerQuery Editor, copy this code into the Advanced Editor, and refer to these steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k9Ly0xOVdJRMtQ31DcyMDJWitUhU9RI3wiLqLG+MVQ0FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unit Number" = _t, #"Defect Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit Number", type text}, {"Defect Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Unit Number", "Defect Date"}, {{"Data", each _},{"MinIndex", each List.Min(_[Index])}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Index"}, {"Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Distinct Key", each if [Index] = [MinIndex] then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "MinIndex"})
in
    #"Removed Columns"

    Query overview in Power BI Desktop - Power BI | Microsoft Learn

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • ichavarria's avatar
    ichavarria
    Solution Specialist
    3 years ago

    To get the distinct count of your tenant records based on a different date, you can use the DAX function DISTINCTCOUNT(). Here's the DAX formula that you can use to calculate the distinct key:

     

    Distinct Key = IF(CALCULATE(DISTINCTCOUNT('Table'[Defect Date]), ALLEXCEPT('Table', 'Table'[Unit Number])) > 1, 1, 0)

     

    In the formula above, 'Table' refers to the name of your table, and Unit Number and Defect Date are the column names. The ALLEXCEPT() function is used to remove all filters from the table except for the Unit Number column. This is necessary to count the distinct dates for each unit.

     

    The CALCULATE() function is used to apply the filter context to the table, which means that it only counts the distinct dates for each unit based on the current filter context.

     

    The IF() function is used to check if the distinct count of dates is greater than 1, which means that there are multiple distinct dates for that unit. If there is more than one distinct date, the formula returns 1, otherwise it returns 0.

     

    You can add this formula to a new column in your table and it should return the Distinct Key that you need.

     

    Best regards, 

    Isaac Chavarria 


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

  • Jorine's avatar
    Jorine
    New Member
    3 years ago

    I tried your formula, it still return me 1 for each date 1/1/2023 but I only need 1 count if the date are same. any way to overcome this? 😣