Add a Calculated Column to a Table in Power BI

0
votes

I am new to power BI, and I am struggling with adding a calculated column to a table which simply looks like this:

enter image description here

The column that I need to add to this table should include a list of distinct Y that comes with each X. For example Type1 in X comes in total with two distinct Y which are a and b. So in the new column a, b should appear for each Type1, and so for all other types in X. Simply like this:

enter image description here

I guess I need to use measures in the New Column, but I do not know which measures and what formula in New Column.

I would be grateful if anyone can help me with how I can add this column Distinct Y coming with each X to my table, or at least would you please let me know if Power BI is at all capable to do this?

Many thanks

1
powerbi

1 Answers

1
votes

You can use the following measures:

Distinct Y for X:

CALCULATE ( 
    CONCATENATEX ( 
        DISTINCT ( Table1[Y] ),
        Table1[Y], 
        ", "
    ),
    ALLEXCEPT ( Table1, Table1[X] )
)

Number of Distinct Y:

CALCULATE ( 
    DISTINCTCOUNT ( Table1[Y] ),
    ALLEXCEPT ( Table1, Table1[X] )
)