DAX formula not working

krishak77 · ‎10-01-2023

Hello Everyone,

Can anyone help me to fix the below formula?

For the below excel formula, i having created measure as below, but it is not working for me.

=IF(AND(D2<0.9,D3>=0.9),A2+(A3-A2)*(0.9-D2)/(D3-D2),0) and it should continue till last row.

Row Labels	Count of Sotat	Count of Sotat2	FR_Per	Fill Rate 90
0	8965	73.96%	73.96%	0
1	1035	8.54%	82.49%	0
2	860	7.09%	89.59%	2.076497696
3	651	5.37%	94.96%	0
4	129	1.06%	96.02%	0
5	77	0.64%	96.66%	0
6	62	0.51%	97.17%	0
7	115	0.95%	98.12%	0
8	40	0.33%	98.45%	0
9	34	0.28%	98.73%	0
10	46	0.38%	99.11%	0
11	11	0.09%	99.20%	0
12	14	0.12%	99.32%	0
13	22	0.18%	99.50%	0
14	23	0.19%	99.69%	0
15	11	0.09%	99.78%	0
20	3	0.02%	99.80%	0
21	13	0.11%	99.91%	0
29	4	0.03%	99.94%	0
30	1	0.01%	99.95%	0
33	1	0.01%	99.96%	0
41	3	0.02%	99.98%	0
52	2	0.02%	100.00%	0

These are measures 'SOTAT Analysis'[A_rownumber] , SOTAT Analysis'[Com_SOTAT] and SOTAT Analysis'[A_SOTAT]

and This is having column SOTAT Analysis'[SOTAT]

A_FR_SOTAT = VAR CurrentRow = 'SOTAT Analysis'[A_rownumber] // Replace 'YourTable' with the actual name of your table and 'Index' with your table's index column

VAR PreviousRowVD = CALCULATE(MAXX('SOTAT Analysis','SOTAT Analysis'[Com_SOTAT]), FILTER('SOTAT Analysis', 'SOTAT Analysis'[SOTAT] = CurrentRow + 1))

VAR PreviousRowA = CALCULATE(MAX('SOTAT Analysis'[Sotat]), FILTER('SOTAT Analysis', 'SOTAT Analysis'[SOTAT] = CurrentRow + 1))

VAR currentRowVD = CALCULATE(MAXX('SOTAT Analysis','SOTAT Analysis'[Com_SOTAT]), FILTER('SOTAT Analysis', 'SOTAT Analysis'[SOTAT] = CurrentRow))

VAR currentRowA = CALCULATE(MAX('SOTAT Analysis'[Sotat]), FILTER('SOTAT Analysis', 'SOTAT Analysis'[SOTAT] = CurrentRow))

RETURN

IF(CurrentRow >=1 && currentRowVD < 0.9 && PreviousRowVD >= 0.9, currentRowA + (PreviousRowA - currentRowA) * (0.9 - (currentRowVD)) / (PreviousRowVD - (currentRowVD)),0

)

Anonymous · ‎10-03-2023

Hi @krishak77,

You can try to use the following measure formula if it suitable for your requirement:

A_FR_SOTAT =
VAR CurrentRow =
    MAX ( 'SOTAT Analysis'[Row Labels] )
VAR nextRowVD =
    CALCULATE (
        MAX ( 'SOTAT Analysis'[Com_SOTAT] ),
        FILTER (
            ALLSELECTED ( 'SOTAT Analysis' ),
            'SOTAT Analysis'[SOTAT] = CurrentRow + 1
        )
    )
VAR nextRowA =
    CALCULATE (
        MAX ( 'SOTAT Analysis'[Sotat] ),
        FILTER (
            ALLSELECTED ( 'SOTAT Analysis' ),
            'SOTAT Analysis'[SOTAT] = CurrentRow + 1
        )
    )
VAR currentRowVD =
    CALCULATE (
        MAX ( 'SOTAT Analysis'[Com_SOTAT] ),
        FILTER (
            ALLSELECTED ( 'SOTAT Analysis' ),
            'SOTAT Analysis'[SOTAT] = CurrentRow
        )
    )
VAR currentRowA =
    CALCULATE (
        MAX ( 'SOTAT Analysis'[Sotat] ),
        FILTER (
            ALLSELECTED ( 'SOTAT Analysis' ),
            'SOTAT Analysis'[SOTAT] = CurrentRow
        )
    )
RETURN
    IF (
        currentRowVD < 0.9
            && nextRowVD >= 0.9,
        currentRowA
            + ( nextRowA - currentRowA )
                * DIVIDE ( 0.9 - currentRowVD, nextRowVD - currentRowVD ),
        0
    )

Regards,

Xiaoxin Sheng

krishak77 · ‎10-04-2023

Hello @Anonymous ,

Thanks for your effort and support.

this code is not working as expected, it is returning as 1 for each row, another Var is working as expected.

As we have percentage in the measure Com_SOTAT, it is coming as roundup with 1 i guess.

Is it possible to get exact percentage value in number?

VAR nextRowVD =
    CALCULATE (
        MAX ( 'SOTAT Analysis'[Com_SOTAT] ),
        FILTER (
            ALLSELECTED ( 'SOTAT Analysis' ),
            'SOTAT Analysis'[SOTAT] = CurrentRow + 1
        )
    )