Forum Discussion

victorbetancurt's avatar
victorbetancurt
Regular Visitor
7 years ago
Solved

Distinct Count with value in same table

Hi everyone. Beforehand, sorry for my bad english. I have a table like this ('Table1'): CODE STATE 1015 A 1016 A 1017 B 1015 B 1016 B 1017 B 1015 C 1015 A 10...
Need Help
Show and Tell
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    Another thing you may want to try then is instead of appending the two data tables, merge them side by side.

     

    So intead of your original table, you end up with something like this:

     

    CODEPREV_STATECUR_STATECHANGED
    1015AATRUE
    1016ABFALSE
    1017BBTRUE

     

    If whatever index you're using for each row stays the same between days, this may be a better way to store your data.  Be sure to check my previous reply for DAX code to solve your original problem

Mariusz's avatar
Mariusz
Community Champion
7 years ago

Hi victorbetancurt 

 

Please see the below M code for Custom Column 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwNFXSUXJUitUBc8yQOeZAjhOMY4rMMUPmYChzRuagmxYLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t, State = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"State", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let c = [Code], s = [State] in 
Table.RowCount( 
    Table.SelectRows( 
        #"Changed Type", 
        each [Code] = c and [State] = s
    )
), Int64.Type)
in
    #"Added Custom"

Regards,
Mariusz

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

victorbetancurt's avatar
victorbetancurt
Regular Visitor
7 years ago

It's not working. The query generates (loading here) a data processing of more than 3 gb.