Forum Discussion
Solved
Counting distinct values
I am working with a dataset which is a output of full joins across 3-4 systems. I am trying to get distinct count of the id's missing in each system System A System B System C AAA A...
Above will return the Count of Missing IDs.
If you want a list of Missing IDs... Goto Modelling Tab and select NEW TABLE
List of ID's in B missing in C = EXCEPT ( FILTER ( ALL ( TableName[SystemB_ID] ), TableName[SystemB_ID] <> BLANK () ), ALL ( TableName[SystemC_ID] ) )
Does this work for you?
let
Query1 = #table(
type table
[
#"SystemA_ID"=text,
#"SystemB_ID"=text,
#"SystemC_ID"=text,
#"System_A_Column1"=number,
#"System_B_Column1"=number,
#"System_C_Column1"=number
],
{
{"ID1",null,"ID1",1,null,1}
, {"ID2","ID2",null,1,1,null}
, {null,"ID3","ID3",null,1,1}
, {"ID4","ID4",null,1,1,null}
, {"ID5","ID5","ID5",1,1,1}
}),
#"Changed Type" = Table.TransformColumnTypes(Query1,{{"SystemA_ID", type text}, {"SystemB_ID", type text}, {"SystemC_ID", type text}, {"System_A_Column1", Int64.Type}, {"System_B_Column1", Int64.Type}, {"System_C_Column1", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "ID", each if [SystemA_ID] <> null then [SystemA_ID] else if [SystemB_ID] <> null then [SystemB_ID] else [SystemC_ID]),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "AMissingInB", each if [SystemA_ID] <> null and [SystemB_ID] = null then [SystemA_ID] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "AMissingInC", each if [SystemA_ID] <> null and [SystemC_ID] = null then [SystemA_ID] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "BMissingInC", each if [SystemB_ID] <> null and [SystemC_ID] = null then [SystemB_ID] else null),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "BMissingInA", each if [SystemB_ID] <> null and [SystemA_ID] = null then [SystemB_ID] else null),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "CMissingInA", each if [SystemC_ID] <> null and [SystemA_ID] = null then [SystemC_ID] else null),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "CMissingInB", each if [SystemC_ID] <> null and [SystemA_ID] = null then [SystemC_ID] else null),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom5", {"SystemA_ID", "SystemB_ID", "SystemC_ID", "System_A_Column1", "System_B_Column1", "System_C_Column1", "ID"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Attribute", "Attribute - Copy"),
#"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "Attribute - Copy", Splitter.SplitTextByPositions({0, 1}, false), {"Attribute - Copy.1", "Attribute - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute - Copy.1", type text}, {"Attribute - Copy.2", type text}}),
#"Split Column by Position1" = Table.SplitColumn(#"Changed Type1", "Attribute - Copy.2", Splitter.SplitTextByPositions({0, 1}, true), {"Attribute - Copy.2.1", "Attribute - Copy.2.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Attribute - Copy.2.1", type text}, {"Attribute - Copy.2.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Attribute - Copy.2.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute - Copy.1", "Source"}, {"Attribute - Copy.2.2", "Target"}})
in
#"Renamed Columns"Distinct IDs is a variation on the same code
let
Query1 = #table(
type table
[
#"SystemA_ID"=text,
#"SystemB_ID"=text,
#"SystemC_ID"=text,
#"System_A_Column1"=number,
#"System_B_Column1"=number,
#"System_C_Column1"=number
],
{
{"ID1",null,"ID1",1,null,1}
, {"ID2","ID2",null,1,1,null}
, {null,"ID3","ID3",null,1,1}
, {"ID4","ID4",null,1,1,null}
, {"ID5","ID5","ID5",1,1,1}
}),
#"Changed Type" = Table.TransformColumnTypes(Query1,{{"SystemA_ID", type text}, {"SystemB_ID", type text}, {"SystemC_ID", type text}, {"System_A_Column1", Int64.Type}, {"System_B_Column1", Int64.Type}, {"System_C_Column1", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "ID", each if [SystemA_ID] <> null then [SystemA_ID] else if [SystemB_ID] <> null then [SystemB_ID] else [SystemC_ID]),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "AMissingInB", each if [SystemA_ID] <> null and [SystemB_ID] = null then [SystemA_ID] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "AMissingInC", each if [SystemA_ID] <> null and [SystemC_ID] = null then [SystemA_ID] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "BMissingInC", each if [SystemB_ID] <> null and [SystemC_ID] = null then [SystemB_ID] else null),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "BMissingInA", each if [SystemB_ID] <> null and [SystemA_ID] = null then [SystemB_ID] else null),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "CMissingInA", each if [SystemC_ID] <> null and [SystemA_ID] = null then [SystemC_ID] else null),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "CMissingInB", each if [SystemC_ID] <> null and [SystemB_ID] = null then [SystemC_ID] else null),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom5", {"SystemA_ID", "SystemB_ID", "SystemC_ID", "System_A_Column1", "System_B_Column1", "System_C_Column1", "ID"}, "Attribute", "Value"),
#"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Columns",{"Value", "Attribute"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Value"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}})
in
#"Grouped Rows"
You can also use the Left Anti Merge (Rows only in the first Query) to get the same results using several queries.
Repeat this type of Query for each check
e..g. AMissinginB
let
Source = Table.NestedJoin(SystemAIDs,{"ID"},SystemBIDs,{"ID"},"SystemBIDs",JoinKind.LeftAnti),
#"Removed Columns" = Table.RemoveColumns(Source,{"SystemBIDs"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Attribute", each "AMissingInB")
in
#"Added Custom"Then Append and group
let
Source = Table.Combine({AMissinginB, AMissinginC, BMissinginC, BMissinginA, CMissinginA, CMissinginB}),
#"Grouped Rows" = Table.Group(Source, {"Attribute"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Attribute", "Attribute - Copy"),
#"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "Attribute - Copy", Splitter.SplitTextByPositions({0, 1}, false), {"Attribute - Copy.1", "Attribute - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute - Copy.1", type text}, {"Attribute - Copy.2", type text}}),
#"Split Column by Position1" = Table.SplitColumn(#"Changed Type1", "Attribute - Copy.2", Splitter.SplitTextByPositions({0, 1}, true), {"Attribute - Copy.2.1", "Attribute - Copy.2.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Attribute - Copy.2.1", type text}, {"Attribute - Copy.2.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Attribute - Copy.2.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute - Copy.1", "Source"}, {"Attribute - Copy.2.2", "MissingFrom"}})
in
#"Renamed Columns"
Distinct IDs is a different group
let
Source = Table.Combine({AMissinginB, AMissinginC, BMissinginC, BMissinginA, CMissinginA, CMissinginB}),
#"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}})
in
#"Grouped Rows"
HI rajul_rstgi
Except function in DAX Returns the rows of one table which do not appear in another table.
Try this MEASURE
ID's in B missing in C =
COUNTROWS (
EXCEPT (
FILTER ( ALL ( TableName[SystemB_ID] ), TableName[SystemB_ID] <> BLANK () ),
ALL ( TableName[SystemC_ID] )
)
)Above will return the Count of Missing IDs.
If you want a list of Missing IDs... Goto Modelling Tab and select NEW TABLE
List of ID's in B missing in C = EXCEPT ( FILTER ( ALL ( TableName[SystemB_ID] ), TableName[SystemB_ID] <> BLANK () ), ALL ( TableName[SystemC_ID] ) )Zubair_Muhammad and stretcharm Thanks a lot for your help with thisl
