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             Y3

X2             Y3         

 

So I want is to create Column C that will show how many unique Y's are per unique X so it should look like this:

Column A Column B Column C

 

X1             Y1             2

 

X1             Y2             2

 

X2             Y3             1

 

X2             Y3             1

 

 

Ofcourse real data cannot be shared.

 

Many thanks for your wisdom 😉

 

  • 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"

     

4 Replies

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

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

     

    Expected result CC =
VAR _filterbyColumnA =
    FILTER ( Data, Data[Column A] = EARLIER ( Data[Column A] ) )
VAR _summarizebyColumnB =
    SUMMARIZE ( _filterbyColumnA, Data[Column B] )
RETURN
    COUNTROWS ( _summarizebyColumnB )
    • 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"