Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
@gauri , what is other expected amount
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |