Is there anyway to extract column name for the value equals 'X' in power BI?

user_19407309 0
Viewed 179

My first ever question on this forum. I am relatively new to Power BI and would really appreciate support from the experts in the community.

I am trying to extract the column names for each values of 'X'. I need to look at columns B to J. Is there a way to do this in power BI.

Appreciate any help. I have attached a screenshot to help understand, let me know if you need more information.

Many Thanks Azhar

Sample data

powerbi measure
1 Answers

Currently your data looks like this:

ES No. ENCL TYPE ELEC
0001 X
0002 X
0003 X

For the sake of demonstration I've simplified it a bit.

From this, you want to unpivot your columns. It's performed in PowerQuery :

  1. Go in PowerQuery (Transform Data) in the ribbon.
  2. In your table, selection columns B to J.
  3. In the ribbon, Transform tab, choose Unpivot Columns.

Your new table looks like this :

ES No. Attribute Value
0001 ENCL
0001 ELEC X
... ... ...
0002 ELEC X

Then, you can create a measure to return the Attribute value for each ES No. where the value is an X. We'll assume there is only one X per ES No.

XCol =
CALCULATE(
    MAX(Table[Attribute]),
    Table[Value] = "X"
)

The result

Note, you can avoid getting a value in the Total lines by tweaking the measure.

user_58872670
Related