DAX for creating new column based on three other columns

[Anonymous]

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?

[Anonymous]

Hi,

Could you please share this data in table format so that it is easy to use.

Thanks.

[Anonymous]

Sorry I've updated it to a table. Is that better? @Anonymous 

[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.

[Anonymous]

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]

Which field is used for the slicer?

[Anonymous]

@Anonymous there are several fields that i have not included in the table that are used as slicers including gender, age, and four others.

[Anonymous]

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() )

[Anonymous]

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 

[Anonymous]

Hi,
If you share a sample of the dataset with the necessary fields and the slicer fields as well we might be able to solve it.
Thanks.