Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All
PowerBI beginner here - have done beginner training and will do intermediate training in a month or so.
Im attempting to create a custom collumn that will return a value from collumn 2, but the value of the custom collumn is dependent on the index number in an index collumn
Rows 8-37 need to reuturn the 2nd value in column 2
Rows 38 to 47 need to return a null value
Rows 48 to 77 need to return the 42nd value in column 2
Rows 78 to 87 need to return a null value
Rows 88 to 117 need to return the 82nd value in column 2
Rows 118 to 127 need to ruturn a null value
and so on until the end of the data (for thousands of rows)
Any advice greatly apprecaited.
Rob
Solved! Go to Solution.
Hi @rgstevens ,
By my tests and research, you could create the calculated columns below to achieve your desired output.
Column 2 = MOD('Data'[Index]-8,40) Column 3 = IF ( Data[Column 2] = 0, CALCULATE ( SUM ( Data[Value] ), FILTER ( Data, Data[Index] = EARLIER ( Data[Index] ) - 6 ) ), IF ( Data[Column 2] > 0 && Data[Column 2] <= 29, 0 ) ) Column 3 = IF ( Data[Column 2] = 0, CALCULATE ( SUM ( Data[Value] ), FILTER ( Data, Data[Index] = EARLIER ( Data[Index] ) - 6 ) ), IF ( Data[Column 2] > 0 && Data[Column 2] <= 29, 0 ) ) Column 5 = CALCULATE(MAX(Data[Column 3]),ALLEXCEPT(Data,Data[Column 4]))
Here is the output.
More details, you could refer to my attachment.
Best Regards,
Cherry
Hi @rgstevens ,
By my tests and research, you could create the calculated columns below to achieve your desired output.
Column 2 = MOD('Data'[Index]-8,40) Column 3 = IF ( Data[Column 2] = 0, CALCULATE ( SUM ( Data[Value] ), FILTER ( Data, Data[Index] = EARLIER ( Data[Index] ) - 6 ) ), IF ( Data[Column 2] > 0 && Data[Column 2] <= 29, 0 ) ) Column 3 = IF ( Data[Column 2] = 0, CALCULATE ( SUM ( Data[Value] ), FILTER ( Data, Data[Index] = EARLIER ( Data[Index] ) - 6 ) ), IF ( Data[Column 2] > 0 && Data[Column 2] <= 29, 0 ) ) Column 5 = CALCULATE(MAX(Data[Column 3]),ALLEXCEPT(Data,Data[Column 4]))
Here is the output.
More details, you could refer to my attachment.
Best Regards,
Cherry
Hi Cherry
Thank you for your work with this. Ive followed it along and can see it works well in the example file you provided. I re-read my original request and see that I misleadingly used the word value. I actually need text returned, Eg. ACH0023. Appologies for this miscommunication on my part.
Rob
Hi @rgstevens ,
I'm not clear about your data sample, but I think the logic should be the same. Please modify my formulas based on your scenario.
If you need other help, you could create another topic so that people who may have the same question can get the solution directly.
Best Regards,
Cherry
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
101 | |
78 | |
76 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |