Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Change values of one row into separate rows

I have the following formula and result of the formula (Please ignore the red lines, as I've changed the names of tables and columns since I'm posting on a public forum): 

 

 

Missing Costs Test =
(if((RELATED(RefTable[Product categories])<>"Cylinder")&&(Output[Total Cost]=0),"Missing Costs Outside, ", "") &

if((RELATED(OutputMaster[Step 1+2])="")&&(OutputEuro[Step 1 per unit]=0)&&(RELATED(OutputMaster[Step 1])<> ""),"Missing Costs on Step 1, ",""))

 

Sample.pngSample 2.PNG

 

The result of the formula I have combines the values from my if statements into one row since I'm using an "&" between the two If statements.  What I really want is a result that looks like the following:

 

Sample 3.PNG

 

Does anyone know how I can accomplish this?  Currently, I'm using the above formula as a new column in my "Output" table but I don't mind creating a new table to have the "Index" and "Missing Costs Test" values and joining back to the "Output" table if necessary.

3 REPLIES 3
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

You can modify above formula:

Missing Costs Test =
 (
    IF (
        ( RELATED ( RefTable[Product categories] ) <> "Cylinder" )
            && ( Output[Total Cost] = 0 ),
        "Missing Costs Outside",
        ""
    )
        & UNICHAR ( 10 )
        & IF (
            ( RELATED ( OutputMaster[Step 1+2] ) = "" )
                && ( OutputEuro[Step 1 per unit] = 0 )
                && ( RELATED ( OutputMaster[Step 1] ) <> "" ),
            "Missing Costs on Step 1",
            ""
        )
)

 

Add it to table visual, remember to turn on "Word Wrap".

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks for the suggestion Yuliana.  However, the result I was looking for was actually seperating the two values into two seperate cells.  The result here gives the perception of two rows, but it is still one value.

 

I was looking for a method where I can filter on a "Missing Costs Test" value and have each unique "Index" for it to show up in a table.  I wouldn't be able to filter for this in the example you have suggested.  The end result I'm looking for is the following if possible:

 

Sample 3.PNG

 

The "Missing Costs Outside" and "Missing Costs on Step 1" are two unique values in this format, and I can filter for either one and find all the Index values for either if aligned this way.

 

Thank you,

Aman Gheewalla

Hi @Anonymous,

 

No. It is not possible to achieve such a output.

 

If you are familiar with Power Query, you could create a custom column "Missing Costs Test" with Power Query. Then, split this column with delimiter, then, unpivot table. Split comma delimited cell into multiple rows, keeping row details

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.