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.
I am trying to create a new column (Variable 2) based off of Time Point, Completed and Variable 1 in the table below. I want the new column to copy the value from Variable 1, only if the count of Completed=1 at each respective time point is greater than 5. Otherwise, Variable 3 will remain blank for that Time Point. So my resulting table would look like this:
ID | Time | Completed | Variable 1 | Variable 2 |
1 | 0 | 1 | 2 | 2 |
1 | 1 | 1 | 1 |
|
1 | 2 | 1 | 4 | 4 |
2 | 0 | 1 | 3 | 3 |
2 | 1 | 0 | 1 |
|
2 | 2 | 1 | 3 | 3 |
3 | 0 | 1 | 2 | 2 |
3 | 1 | 0 | 0 |
|
3 | 2 | 0 | 1 | 1 |
4 | 0 | 1 | 3 | 3 |
4 | 1 | 0 | 2 |
|
4 | 2 | 1 | 2 | 2 |
5 | 0 | 1 | 2 | 2 |
5 | 1 | 0 | 3 |
|
5 | 2 | 0 | 1 | 1 |
6 | 0 | 1 | 1 | 1 |
6 | 1 | 0 | 5 |
|
6 | 2 | 1 | 0 | 0 |
7 | 0 | 1 | 4 | 4 |
7 | 1 | 1 | 2 |
|
7 | 2 | 1 | 3 | 3 |
8 | 0 | 1 | 3 | 3 |
8 | 1 | 0 | 1 |
|
8 | 2 | 0 | 2 | 2 |
9 | 0 | 1 | 1 | 1 |
9 | 1 | 1 | 4 |
|
9 | 2 | 0 | 1 | 1 |
10 | 0 | 1 | 2 | 2 |
10 | 1 | 1 | 3 |
|
10 | 2 | 1 | 3 | 3 |
As you can see the count of Completed=1 at Time=1 is only 4, so the value for Variable 2 at time=1 is blank. Does anyone know what the DAX code would be to create this column?
Hi,
Could you please share this data in table format so that it is easy to use.
Thanks.
Sorry I've updated it to a table. Is that better? @Anonymous
Hi,
Try using the below DAX to create the calculated column.
Variable2 = VAR FilteredRows = CALCULATE ( COUNTROWS( Data ), ALLEXCEPT(Data, Data[Time] ), Data[Complete] == 1 ) RETURN IF( FilteredRows > 5, Data[Variable1], BLANK() )
Thanks.
Thanks so much for your help @Anonymous
The DAX doesn't seem to be working as I had hoped. I forgot to mention that the original dataset has a Completed=1 count greater than 5, but when you use a slicer to slice the data the count can fall below 5, and at that point, I want the value to be changed to blank. Is that possible?
@Anonymous there are several fields that i have not included in the table that are used as slicers including gender, age, and four others.
Hi,
Check if this works. You will need to put the slicer fields in ALLEXCEPT function.
Thanks.
Variable2Col = VAR FilteredRows = CALCULATE ( COUNTROWS( Data ), ALLEXCEPT(Data, Data[Time],Data[slicer] ), //append your slicer fields here Data[Completed] == 1 ) RETURN IF( FilteredRows >=5, Data[Variable1], BLANK() )
I tried it and it's still not working as I had wanted it to. I'm not exactly sure what's going on... Thank you so much for your help though, I really do appreciate it. @Anonymous
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 |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |