Repeating values based on condition in DAX

[RSip]

Greetings,

I have this problem which I am unable to solve by myself:

I have a table which holds the information of different test subjects, the data is grouped, ranked and sorted. The column "strength35" is used to calculate columns "MIN", "MAX", "REDLINE", then columns "strength15" and "AVERAGE" are used to compare with these values. Rows are ranked by date and group, but for the sake of simplicity only a single group is taken into account at the moment.

I am trying to create a column in DAX, that would keep returning the first value from the column "strength35" until the value in column "strength15" is lower than "MIN" or higher than "MAX" or the value in column "AVERAGE" is lower than "REDLINE", then a new value from "strength35" should be picked, which would need to be returned every row until again, the condition is met and a new value must be picked. First 34 rows of information needed to be skipped but not filtered away that is why I used "IF" function in such a way:

NEWCOLUMN =
VAR str35 = table[strength35]
VAR str15 = table[strength15]
VAR MIN = esama_deviacija * 0.63
VAR MAX = esama_deviacija * 1.37
VAR REDLINE = 40 + 1.48 * esama_deviacija
VAR TEST =
IF(table[Rank(desc)]<35,BLANK(),
IF(table[Rank(desc)]=35,str35,
IF(MIN<=str15 && MAX>=str15 && REDLINE<=table[AVERAGE],strength35,str35)))
RETURN TEST

When writting this I somehow thought that "strength35" would be calculated for each row and str35 would be calculated only when the condition is no longer met, but I was completely wrong and I am unsure how to fix it.

The table i got:

DATE	RANK	strength35	MIN	strength15	MAX	REDLINE	AVERAGE	NEWCOLUMN
2023-06-11	44	5,71	3,60	3,72	7,83	48,45	53,18	5,71
2023-06-10	43	5,73	3,61	3,68	7,85	48,48	53,18	5,73
2023-06-09	42	11,55	7,28	7,96	15,83	57,10	53,18	5,95
2023-06-08	41	6,31	3,97	4,91	8,64	49,33	53,18	6,31
2023-06-07	40	6,56	4,13	5,02	8,98	49,71	53,18	6,56
2023-05-21	39	5,31	3,35	4,80	7,28	47,86	53,18	5,31
2023-05-20	38	4,85	3,06	4,96	6,65	47,18	53,18	4,85
2023-05-19	37	4,94	3,11	5,47	6,77	47,32	53,18	4,94
2023-05-10	36	8,25	5,20	6,19	11,31	52,21	53,18	8,25
2023-05-09	35	8,35	5,26	6,00	11,44	52,36	53,18	8,35

The  table I was trying to get:

DATE	RANK	strength35	MIN	strength15	MAX	REDLINE	AVERAGE	NEWCOLUMN
2023-06-11	44	5,71	3,60	3,72	7,83	48,45	53,18	5,73
2023-06-10	43	5,73	3,61	3,68	7,85	48,48	53,18	5,73
2023-06-09	42	11,55	7,28	7,96	15,83	57,10	53,18	11,55
2023-06-08	41	6,31	3,97	4,91	8,64	49,33	53,18	4,85
2023-06-07	40	6,56	4,13	5,02	8,98	49,71	53,18	4,85
2023-05-21	39	5,31	3,35	4,80	7,28	47,86	53,18	4,85
2023-05-20	38	4,85	3,06	4,96	6,65	47,18	53,18	4,85
2023-05-19	37	4,94	3,11	5,47	6,77	47,32	53,18	8,35
2023-05-10	36	8,25	5,20	6,19	11,31	52,21	53,18	8,35
2023-05-09	35	8,35	5,26	6,00	11,44	52,36	53,18	8,35

For some reason altering table options gives errors and prevents me from posting, so I boldened characters every second column for clarity.

If anyone has any suggestions or solutions, I would be very grateful.

[gmsamborn]

Hi @RSip 

I took another look at [NEWCOLUMN] and realized that it will ALWAYS equal 'Table'[strength35].

In the first line, you set str35 to 'Table'[strength35] meaning that the variable won't change.  Also, there is nothing in your DAX that would change the context, that would change the returned value for 'Table'[strength35] in any way.

NEWCOLUMN = 
VAR _str35 = 'Table'[strength35]
VAR _str15 = 'Table'[strength15]
VAR _MINIMUM = _str35 * 0.63
VAR _MAXIMUM = _str35 * 1.37
VAR _REDLINE = 40 + ( 1.48 * _str35 )
VAR _TEST =
    IF (
        'Table'[RANK] < 35,
        BLANK (),
        IF (
            'Table'[RANK] = 35,
            'Table'[strength35],
            IF (
                _MINIMUM <= _str15
                    && _MAXIMUM >= _str15
                    && _REDLINE <= 'Table'[AVERAGE],
                _str35,
                'Table'[strength35]
            )
        )
    )
RETURN
    _str35

Let me know if you have any questions.

[v-nuoc-msft]

Hi @RSip 

I tested your problem and found some issues.

For the result you want, "str15" matches the condition you set in dax, i.e. a data rank of 38.

According to your description, NEWCOLUMN should output "835" to be logical.

However, you want it to output its own value "str35".

This is what I don't understand. Do you have another judgment condition present?

It would be best to provide the pbix file if you can and be careful to remove sensitive data.

This will allow me to help you in more detail.

Regards,

Nono Chen

[RSip]

Greetings,

I have made a dummy table in PowerBi and created "NEWCOLUMN". I have attached a link to pbix file in my dropbox. I hope this helps to answer my question. Thank you in advance.

Link to dummy table in dropbox 

[RSip]

I apologise for the late reply.  I noticed that the formula I attached was not the one I used in my PowerBi, this is what I used:

VAR str35 = table[strength35]
VAR str15 = table[strength15]
VAR MINIMUM = str35 * 0.63
VAR MAXIMUM = str35 * 1.37
VAR REDLINE = 40 + 1.48 * str35
VAR TEST =
    IF (
        table[Rank(desc)] < 35,
        BLANK (),
        IF (
            table[Rank(desc)] = 35,
            table[strength35],
            IF (
                MIN <= str15
                    && MAX >= str15
                    && REDLINE <= table[AVERAGE],
                str35,
                table[strength35]
            )
        )
    )
RETURN
    TEST

In a row of rank 38, while 'strength35' and 'strength15' almost match, MIN value from row ranked 35 is 5.26 which is higher than 4.96 'strength15' value in row ranked 38. What I am trying to achieve in this table is to find a value, calculate minimum, maximum and redline boundries for it and use these values until the boundries are crossed, then get a new value and calculate its boundries and so on. 

I do not have another judgement condition present.

In a day or two I should be able to create a pbix file with dummy data.

[gmsamborn]

Hi @RSip 

I imported your data and tried to create your NEWCOLUMN.

I noticed that you were using MIN and MAX as variable names.  This isn't allowed because they are reserved keywords.

I'm stuck on determining what 'esama_deviacija' is.  Because it isn't enclosed in [ and ], rules out it being a measure or column.  The only things I can think of are a) a variable name you haven't defined or b) a table in which case you would have to refer to a column.

Can you explain 'esama_deviacija'?

[RSip]

Hello,

I have created a dummy table with "NEWCOLUMN". I'm attaching the link to pbix file in dropbox below:

Dummy table in dropbox .I hope this will be of some help. Thank you in advance.

[RSip]

I apologise for late reply. 'esama_deviacija' is a variable name that I used in my PowerBi file, that I mistakenly did not rename for this example. 'esama_deviacija' should have been named str35. This should work:

NEWCOLUMN =
VAR str35 = table[strength35]
VAR str15 = table[strength15]
VAR MINIMUM = str35 * 0.63
VAR MAXIMUM = str35 * 1.37
VAR REDLINE = 40 + 1.48 * str35
VAR TEST =
    IF (
        table[Rank(desc)] < 35,
        BLANK (),
        IF (
            table[Rank(desc)] = 35,
            table[strength35],
            IF (
                MIN <= str15
                    && MAX >= str15
                    && REDLINE <= table[AVERAGE],
                str35,
                table[strength35]
            )
        )
    )
RETURN
    TEST

Hopefuly this will clear up things.