The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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, ",""))
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:
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.
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".
Best regards,
Yuliana Gu
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:
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
User | Count |
---|---|
79 | |
78 | |
37 | |
33 | |
31 |
User | Count |
---|---|
93 | |
81 | |
59 | |
49 | |
48 |