Difficulty in mapping of columns for certain data of residents

[gauri]

Hello,

In my screen shot 1 i have column named as "coins_artyp" which has value IXA and there are two amounts attached with it so i am able to get only 1056 amount i am not able to get the other amount.

I have created a calculated calculated column in power bi

to get the amount using this:

SUMX(summarize(FILTER(ar_tran_room,( ar_tran_room[coins_artyp] = ar_tran_rcpt[artyp] )&& ar_tran_room[cono] = ar_tran_rcpt[cono] && ar_tran_room[resno] = ar_tran_rcpt[resno] && ar_tran_room[gl_period] = ar_tran_rcpt[svc_date] ),ar_tran_room[cono], ar_tran_room[resno], ar_tran_room[gl_period] , "_max" ,max(ar_tran_room[coins_amt])),[_max])

The above dax is working good for some residents but its giving some kind of anomaly for some of the residents. I am not able to figure out why this is causing issue

I am getting the  amount correct by just adding one paramater in dax which i was missing, but there is an issue now , i have this dax :

new ancillary revenue(chrgcd_artyp) = SUMX(SUMMARIZE(FILTER('aht ar_tran_ancl (2)',( 'aht ar_tran_ancl (2)'[cono] = ar_tran_rcpt[cono]) && 'aht ar_tran_ancl (2)'[resno] = ar_tran_rcpt[resno] && 'aht ar_tran_ancl (2)'[chrgcd_artyp] = ar_tran_rcpt[artyp] && 'aht ar_tran_ancl (2)'[gl_period] = ar_tran_rcpt[svc_date] && 'aht ar_tran_ancl (2)'[adjcd] = 0), 'aht ar_tran_ancl (2)'[cono], 'aht ar_tran_ancl (2)'[resno], 'aht ar_tran_ancl (2)'[chrgcd_artyp], 'aht ar_tran_ancl (2)'[gl_period] ,"_max2",SUM('aht ar_tran_ancl (2)'[Ancillary Revenue(chrgcd_Artyp)])),[_max2] ).

here there is a clause which i have highlightes in Bold, so it it pulling only those artypes which are present in the ar_tran_rcpt table. For eg. If i have an artyp names as MXB in ar_tran_ancl table but not in ar_tran_rcpt table then MXB is not getting pulled. I want to build such a dax where it takes all the artypes from ar_tran_ancl table and ar_tran_rcpt table.

[amitchandak]

@gauri , what is other expected amount

[gauri]

@amitchandak  880

[v-deddai1-msft · Accepted solution]

Hi @gauri ,

Would you please explain if you want to get the second highest value in 'coins_amt' column?

If so, please try to add a rank column in the 'ar_tran_room' table:

Rank = RANKX(ALL( 'ar_tran_room' [coins_artyp]),'ar_tran_room' [coins_amt],,DESC)

Then modify your measure to:

Measure =

SUMX (

    SUMMARIZE (

        FILTER (

            ar_tran_room,

            ( ar_tran_room[coins_artyp] = ar_tran_rcpt[artyp] )

                && ar_tran_room[cono] = ar_tran_rcpt[cono]

                && ar_tran_room[resno] = ar_tran_rcpt[resno]

                && ar_tran_room[gl_period] = ar_tran_rcpt[svc_date]

        ),

        ar_tran_room[cono],

        ar_tran_room[resno],

        ar_tran_room[gl_period],

        "_max", CALCULATE (

            MAX ( ar_tran_room[coins_amt] ),

            FILTER ( ALL ( ar_tran_room[coins_artyp] ), ar_tran_room[rank] = 2 )

        )

    ),

    [_max]

)

Best Regards,

Dedmon Dai