DAX: A Distinct count challenge

[OLM]

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]

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]

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 · Accepted solution]

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]

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