Forum Discussion

lucadelicio's avatar
lucadelicio
Impactful Individual
1 year ago
Solved

Calculation

Goodmorning Community, i need your precious help. I uploaded my dataset in xlsx. Is not the complete dataset but is only the rows with FLG_KRI_08 = 1 e FLG_CONSOCIATA = 1 so we have to consider...
Need Help
  • lucadelicio's avatar
    lucadelicio
    1 year ago

    Thank you for your answer.
    The logic it's clear but the formula was difficult to find.
    With the help of artificial intelligence i was arrived to the solution with the 

    IN
            SELECTCOLUMNS()
    option thank you everybody for your help!

    COUNTROWS(
        FILTER(
            D_STORICO_VARIANTI,
            D_STORICO_VARIANTI[FLG_KRI_08] = 1
            && D_STORICO_VARIANTI[FLG_CONSOCIATA] = 1
            && D_STORICO_VARIANTI[DAT_LANCIO_RILASCIO] <= MAX('DIM Data Storico Varianti'[Data])
            && D_STORICO_VARIANTI[SKY_KRI_CONTRATTO_PASSIVO] IN
            SELECTCOLUMNS(
                FILTER(
                    D_STORICO_VARIANTI,
                    D_STORICO_VARIANTI[DAT_LANCIO_RILASCIO] >= MIN('DIM Data Storico Varianti'[Data])
                    && D_STORICO_VARIANTI[DAT_LANCIO_RILASCIO] <= MAX('DIM Data Storico Varianti'[Data])
                    && D_STORICO_VARIANTI[FLG_KRI_08] = 1
                    && D_STORICO_VARIANTI[FLG_CONSOCIATA] = 1
                ),
                "SKY_KRI_CONTRATTO_PASSIVO", D_STORICO_VARIANTI[SKY_KRI_CONTRATTO_PASSIVO]
            )
        )
    )
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi lucadelicio,

 

the issue is that the total row doesn't respect the filter on the two flags because Values( ) in the total context includes all contracts, even those not matching the flags.

To fix this

  • Isolate only the contracts from the selected year that also have both flags = 1.

  • Use that filtered list for both the individual rows and the total.

 

Regards,

Vinay 

lucadelicio's avatar
lucadelicio
Impactful Individual
1 year ago

Thank you for your answer.
The logic it's clear but the formula was difficult to find.
With the help of artificial intelligence i was arrived to the solution with the 

IN
        SELECTCOLUMNS()
option thank you everybody for your help!

COUNTROWS(
    FILTER(
        D_STORICO_VARIANTI,
        D_STORICO_VARIANTI[FLG_KRI_08] = 1
        && D_STORICO_VARIANTI[FLG_CONSOCIATA] = 1
        && D_STORICO_VARIANTI[DAT_LANCIO_RILASCIO] <= MAX('DIM Data Storico Varianti'[Data])
        && D_STORICO_VARIANTI[SKY_KRI_CONTRATTO_PASSIVO] IN
        SELECTCOLUMNS(
            FILTER(
                D_STORICO_VARIANTI,
                D_STORICO_VARIANTI[DAT_LANCIO_RILASCIO] >= MIN('DIM Data Storico Varianti'[Data])
                && D_STORICO_VARIANTI[DAT_LANCIO_RILASCIO] <= MAX('DIM Data Storico Varianti'[Data])
                && D_STORICO_VARIANTI[FLG_KRI_08] = 1
                && D_STORICO_VARIANTI[FLG_CONSOCIATA] = 1
            ),
            "SKY_KRI_CONTRATTO_PASSIVO", D_STORICO_VARIANTI[SKY_KRI_CONTRATTO_PASSIVO]
        )
    )
)