Forum Discussion

OLM's avatar
OLM
Icon for Helper I rankHelper I
3 years ago
Solved

DAX: A Distinct count challenge

Dear PBI community, My DAX challenge: In a table I have an column A with X value which can apply to Column B with 2 Y values Table_1 Column A Column B X1             Y1 X1             Y2 X2    ...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    In my opinion, using this to create another column in another table is also doable by DAX.

    However, the below is for creating the column in Power Query Editor.
    Please check the below picture and the attached pbix file's Power Query Editor.

     

     

    let
    Source = Data_Source,
    #"Grouped Rows" = Table.Group(Source, {"Column A"}, {{"Group", each _, type table [Column A=text, Column B=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Expected result", each Table.Group ( [Group], {"Column A"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}} )),
    #"Expanded Group" = Table.ExpandTableColumn(#"Added Custom", "Group", {"Column B"}, {"Column B"}),
    #"Expanded Expected result" = Table.ExpandTableColumn(#"Expanded Group", "Expected result", {"Count"}, {"Count"})
in
    #"Expanded Expected result"

     

OLM's avatar
OLM
Icon for Helper I rankHelper I
3 years ago

Great Jihwan_Kim 

 

My next Q: I want to pull this column to another table therefore require merging. This means I need this column coded in Power Query. 

 

Can you translate it into M code?

Best

Liam

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

In my opinion, using this to create another column in another table is also doable by DAX.

However, the below is for creating the column in Power Query Editor.
Please check the below picture and the attached pbix file's Power Query Editor.

 

 

let
    Source = Data_Source,
    #"Grouped Rows" = Table.Group(Source, {"Column A"}, {{"Group", each _, type table [Column A=text, Column B=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Expected result", each Table.Group ( [Group], {"Column A"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}} )),
    #"Expanded Group" = Table.ExpandTableColumn(#"Added Custom", "Group", {"Column B"}, {"Column B"}),
    #"Expanded Expected result" = Table.ExpandTableColumn(#"Expanded Group", "Expected result", {"Count"}, {"Count"})
in
    #"Expanded Expected result"

 

  • OLM's avatar
    OLM
    Icon for Helper I rankHelper I
    3 years ago

    Many thanks,  via power query I can merge, therefore :).