Customer Performance Status M.O.M

[kkrj.ankit]

Hi All,

I’ve created the below relationship, and I want to extract performance from the table Bounce and DPD to my master data (ECGLS), both have customer identification no as Account Number.
I want to get BounceStatus from Bounce table and Dpd from Dpd table of the customer in the matrix as per user needs against the Account_Number, Industry, Sales Manager, branch , etc i.e. all the data available in ECGLS.

What I actually want to achieve is something like this and also want my data to reflect changes as per the filters or if any other variable is used by the user instead of loan number like (Industry, Sales Manager, Branch, etc) and which can work with the filters given in the file already or if any new filter is added.

Though this is not giving correct results to me. But I want to achieve this whatever filter the user is selecting.

I’m attaching the pbix as well. Please let me know what I’m missing here.

Report_Test.pbix (422.0 KB)

Thanks for all the help!

[kkrj.ankit]

Hi All,

I used the below measure, is it the right approach to achieve the results?

CALCULATE( SELECTEDVALUE( Bounce[BounceStatus] , “Not Presented” ) ,
CROSSFILTER( ECGLS[Account Number] , Bounce[Account_Number] , Both),
USERELATIONSHIP( ‘Calendar’[Date] , Bounce[schdate] ))

Thanks,
Ankit

[MK3010]

Hi @kkrj.ankit

Can not validate the output as you said your output is wrong.

If you are looking for below output where it’s get updated based on selection.

Here you just want to display the value and you are not doing any kind of calculation so we would not require the Calculate function.

Now after seeing your model I believe we can modify as below.

And then you can just use below DAX code.

BncTbD = 
SELECTEDVALUE(Bounce[BounceStatus]) 

DpdTblD = 
SELECTEDVALUE(DPD[Dpd])

After you put these measures to visuals then you will get output as below. I have included both the Date and Industry as slicer to show that this is working.

Hope this helps you.

[kkrj.ankit]

Thanks @MK3010 for your time.
Reading yor post, I believe I was doing it the right way.
I hope I can get more solutions so that I know different ways to solve that.

Thanks
Ankit

[kkrj.ankit]

Hello!!
Can anyone give a solution for this?

[Hemantsingh]

HI @kkrj.ankit

We cannot use selectedvalue function like this with calculate. Also, i went through your model but still not able to figure out what exactly you are trying to do. Can you make a mock up of the end result you want to achieve in an excel sheet??

Regards,
Hemant

[kkrj.ankit]

Hi @Hemantsingh

As shared by @MK3010 for now I want to display status against the lans, later on I’ll be adding the values for those status and would like to achieve their sum, % over m.o.m and other calculations based on that.
So, I believe if I’ll be able to get the status against the ids I can do other calculations as well.

Below is the result I’m getting and its correct as per the data available in Dpd and Bounce table.

Thanks,
Ankit

[MK3010]

Hi @kkrj.ankit

As per seeing the output you have provided and the data what you had shared are different.

The solution which I had shared is correct but you are still looking for another answers with different approach ?

Also in Bucket# you are referencing column “Bucket” not “dpd” of DPD table which you had mentioned earlier. I am just updating the formula to match with your screen shot. Just the value are different as there was not data with value Not Due in the dataset.

Bounce# = 
SELECTEDVALUE(Bounce[BounceStatus])

Bucket# = 
SELECTEDVALUE(DPD[Bucket]) 

Below is the output

Thanks
Mukesh

[datazoe]

@kkrj.ankit I think you are trying to get something like this:

I did it by disconnecting the date table (so all measures utilizing the previous default relationship would need to be built with userelationship – and I suggest a calculation group.).

Test 1 =
VAR _bounce = if(
    isblank( SELECTEDVALUE( Bounce[BounceStatus] ) ),
    blank(),
    CALCULATE(
        min( Bounce[BounceStatus] ),
        USERELATIONSHIP( Bounce[schdate],'Calendar'[Date] )
    )
) 

VAR _bucket = if(
    isblank( SELECTEDVALUE( DPD[Bucket] ) ),
    blank(),
    CALCULATE( min( DPD[Bucket] ), USERELATIONSHIP( DPD[edl_date],'Calendar'[Date] ) )
) 

return 
if(
    isblank( _bounce ) &&
    isblank( _bucket ),
    blank(),
    COALESCE( _bounce, "NA" ) & "/" & COALESCE( _bucket, "NA" )
)

[EnterpriseDNA]

Hi @kkrj.ankit, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!