topic Re: DAX formula not working in DAX Commands and Tips

DAX formula not working

krishak77 — Sun, 01 Oct 2023 16:25:23 GMT

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

)

Re: DAX formula not working

Anonymous — Wed, 04 Oct 2023 02:30:47 GMT

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

Re: DAX formula not working

krishak77 — Wed, 04 Oct 2023 07:27:09 GMT

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

Re: DAX formula not working

Anonymous — Thu, 05 Oct 2023 07:32:35 GMT

HI krishak77,

AFAIK, the max function does not include round features, they should base on the table field data type definitions.

What data type that you defined on the ‘Com_SOTAT’ filed? Can you please share some dummy data to test?

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: DAX formula not working

krishak77 — Fri, 06 Oct 2023 18:13:34 GMT

Hello Anonymous

Issue is solved, but really appreciate your support.