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,
I am trying to use if or switch logic to complete the folllowing. I work for a collections call center and customers will roll throught these things we call buckets if they become delinquent or they will "cure" if they pay off the loan.
I created quick measures to compare each year to the previous. However, the method I chose to use is creating a new column and creating duplicate customers (loan_num) for march (I circled an example in blue).
I want to be able to compare there statuses for the three months without March creating a new row, which is causing what I wrote below to be innacurate.
Any takers on what I can do to improve this logic?
Solved! Go to Solution.
Hi @Anonymous
You can create measures "collected" and "cure".
Hi @Anonymous ,
Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Hi @Anonymous
Sorry if I miss understood your requirement, but the extra row is introduced by adding CCR to your Table visual, so once removed it will not add an extra row.
Hello,
Yup that was what it was. That new column is creating duplicates. Unfortunatly, I need the calculations for the next visual so that we can see how many loans "collected", "cure", or remained delinquent. Do you have any suggestions on the best way to do this?
Thanks for taking a look at this! I have been working on this for a week!
Hi @Anonymous
You can create measures "collected" and "cure".
Hi I am back to this and now I cannot get the measures to show in a visual. Is it because they are a string?
collect = SWITCH (TRUE(),
AND([status for February 2020] = 3, [status for March 2020] = 2), "collect",
AND([status for February 2020] = 2, [status for March 2020] = 1), "collect",
" "
)
cure = SWITCH (TRUE(),
AND([status for February 2020] = 1, [status for March 2020] = 0), "cure",
AND([status for February 2020] = 2, [status for March 2020] = 0), "cure",
AND([status for February 2020] = 3, [status for March 2020] = 0), "cure",
"roll"
)
stab = SWITCH (TRUE(),
AND([status for February 2020] = 1, [status for March 2020] = 1), "stab",
AND([status for February 2020] = 2, [status for March 2020] = 2), "stab",
AND([status for February 2020] = 3, [status for March 2020] = 3), "stab",
" "
)
I want to see how many of them for the month of each. They keep moving to tooltips. Hopefully that makes sense.
Figured it out!
CollectCSUM =
SUMX(
'Roll Rates',
SWITCH (
TRUE(),
AND([status for February 2020] = 3, [status for March 2020] = 2), 1,
AND([status for February 2020] = 2, [status for March 2020] = 1), 1,
0
)
)
stabSUM =
SUMX(
'Roll Rates',
SWITCH (
TRUE(),
AND([status for February 2020] = 1, [status for March 2020] = 1), 1,
AND([status for February 2020] = 2, [status for March 2020] = 2), 1,
AND([status for February 2020] = 3, [status for March 2020] = 3), 1,
0
))
CureSUM =
SUMX(
'Roll Rates',
SWITCH (TRUE(),
AND([status for February 2020] = 1, [status for March 2020] = 0), 1,
AND([status for February 2020] = 2, [status for March 2020] = 0), 1,
AND([status for February 2020] = 3, [status for March 2020] = 0), 1,
0
)
)
RollSUM =
SUMX(
'Roll Rates',
SWITCH (TRUE(),
AND([status for February 2020] = 1, [status for March 2020] = 2), 1,
AND([status for February 2020] = 2, [status for March 2020] = 3), 1,
AND([status for February 2020] = 3, [status for March 2020] = 4), 1,
0
)
)
except they run super slow. There are about 22 million rows of data.
Ahh okay! SO seperate my logic out into two measure. That makes sense! Let me try that and I will mark your response as the answer!
Thank you!!!
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.