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 fellow BI-enthusiastics,
I'm struggeling with a complex issue and hopefully someone can help me out.
I'm trying to create groups based on a unstructed set that have some self defined events.
Definition of event =
If Index-1 or Index-2 has a value then it's an event and
if Index+1 or Index+2 has a value then it's an event.
Basically I want to scan and group the indexes together to make an Unique event. Hopefully the screenshot explains what I need
I have came up with the following DAX, but that doesn't quite give me the results I need.
IF([EXCEEDED_LIMIT]<>BLANK(); CALCULATE(CONCATENATEX(Tabel1;[Index];"|";[Index];ASC); FILTER(Tabel1; Tabel1[Index]>=EARLIER(Tabel1[Index])-1 && Tabel1[Index]<=EARLIER(Tabel1[Index])+1) ))
Hopefully someone posses' more skills then I do and can help me out. This would be highly appreciated!!
Sample file is here : Table1.xlsx
Solved! Go to Solution.
Hi Nico,
one solution could be to use (various) custom columns to get to unique identifiers.I have for testing purposes now split it into several columns, but most probably can be done in much more condensed way.
1) Group Ind
Hope this helps
BR
Jeroen Heerschop
Get Responsive
Hi @BI-Geniuz ,
According to my test, I can only get the result below at present. This is my PBIX file. I will let you know as soon as I have other solutions.
Best Regards,
Icey Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Icey,
thanks for helping out this far! highy appreciated and it's a tough one indeed 😛
Update:
I also got the flagging code wrapped in here. Maybe that helps a little bit?
var Index1 = LOOKUPVALUE([EXCEEDED_LIMIT];[Index];[Index]+1) var Index2 = LOOKUPVALUE([EXCEEDED_LIMIT];[Index];[Index]-1) return IF([EXCEEDED_LIMIT]<>BLANK();1; IF([EXCEEDED_LIMIT]=BLANK(); IF(Index1<>BLANK() && Index2<>BLANK();1;BLANK())))
Hi Nico,
one solution could be to use (various) custom columns to get to unique identifiers.I have for testing purposes now split it into several columns, but most probably can be done in much more condensed way.
1) Group Ind
Hope this helps
BR
Jeroen Heerschop
Get Responsive
Hi Jeroen, thanks a lot this is exactly what I needed. I followed your logic and understood the principle.
It works like a charm and for my own practice I have replicated your logic and make some DAX statements myself just for practicing.
Again thanks a lot Jeroen and I have attached your PBIX with some replicated code as well
PBIX
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |