Pattern identifying

[MintuBaruah]

Hello @all

I have a table with the following data(Table1):

Table 1: 

Acc.	Cred	Deb	Date
A	1000		12/12/2020
B	20000		1/11/2020
B		20000	1/11/2020
C	550		3/10/2020
D		6000	4/10/2020

I need to identify the pattern where the "cred" and "deb" are happening at the same date for the same amount(i.e. the amount that is credited is being debited at the same date).

And after identifying it should show me in True() and False().

Please help resolve this query.

Thanks.

[v-eqin-msft · Accepted solution]

Hi @MintuBaruah ,

Sorry for my misunderstanding,please modify the True or False measure :

True or False = 
var _cred=CALCULATE( FIRSTNONBLANK('Table1'[Cred],TRUE()),ALLEXCEPT(Table1,Table1[Date]))
var _deb=CALCULATE( FIRSTNONBLANK('Table1'[Deb],TRUE()),ALLEXCEPT(Table1,Table1[Date]))
return IF(_cred=_deb,TRUE(),FALSE())

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-eqin-msft]

Hi @MintuBaruah ,

According to your description, only set TRUE if Cred and Deb occur on the same day and the amount is the same, and then change the background color to red when it is true, right?

If so , please try:

True or False =
VAR _credDate =
    CALCULATE (
        FIRSTNONBLANK ( 'Table1'[Date], TRUE () ),
        FILTER ( ALLEXCEPT ( Table1, Table1[Acc.] ), 'Table1'[Cred] <> BLANK () )
    )
VAR _debDate =
    CALCULATE (
        LASTNONBLANK ( 'Table1'[Date], TRUE () ),
        FILTER ( ALLEXCEPT ( Table1, Table1[Acc.] ), 'Table1'[Deb] <> BLANK () )
    )
VAR _credAmount =
    CALCULATE ( SUM ( Table1[Cred] ), ALLEXCEPT ( Table1, Table1[Acc.] ) )
VAR _debAmount =
    CALCULATE ( SUM ( Table1[Deb] ), ALLEXCEPT ( Table1, Table1[Acc.] ) )
RETURN
    IF ( _credDate = _debDate && _credAmount = _debAmount, TRUE (), FALSE () )

Then create a color measure for conditional formatting:

Color = IF([True or False]=TRUE(),"Red") 

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MintuBaruah]

Hi @v-eqin-msft 

Thank you for the reply. I tried your solution but it is not working on my side.

Are you taking into consideration that "Acc." should be the same, if so that is not correct as there can be transactions from different "Acc." also.

eg:

Acc.	Cred	Deb	Date
A	1000		12/12/2020
B	20000		1/11/2020
E		20000	1/11/2020
C	550		3/10/2020
D		6000	4/10/2020

Regards,

Mintu Baruah

[selimovd]

Hey @MintuBaruah ,

try the following measure. This should show you TRUE when the date has both values and FALSE when it doesn't:

Date With Cred and Deb =
VAR vCred =
    CALCULATE(
        SUM( 'Table 1'[Cred] ),
        ALL( 'Table 1'[Acc.] )
    )
VAR vDeb =
    CALCULATE(
        SUM( 'Table 1'[Deb] ),
        ALL( 'Table 1'[Acc.] )
    )
RETURN
    IF( vCred <> BLANK() && vDeb <> BLANK(),
        TRUE(),
        FALSE()
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[MintuBaruah]

hi @selimovd 

Thank you for the answer.

Can we also highlight the rows after the condition gets True().

for eg. in Red color.

[selimovd]

Hey @MintuBaruah ,

yes, that's also possible with conditional formatting.

Create a measure that returns the color for the format. You can also use hex values if you like that more:

Data Color = 
IF(
    [Date With Cred and Deb] = TRUE(),
    "red",
    BLANK()
)

This measure you can then use for the formatting:

Conditional table formatting in Power BI Desktop - Power BI | Microsoft Docs

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[MintuBaruah]

Hello @selimovd 

Upon applying the conditional formatting, I noticed that it is identifying data according to "Acc." Header and because of that, it is highlighting rows from different dates too.

I wanted to highlight the data related to the same date.

"I need to identify the pattern where the "cred" and "deb" are happening at the same date for the same amount(i.e. the amount that is credited is being debited at the same date)."

Regards,

Mintu Baruah

[selimovd]

Hey @MintuBaruah ,

sorry, I didn't understand your last post.

What is not working?

Best regards

Denis

[MintuBaruah]

Hi  @selimovd 

Table: 

Acc.	Cred	Deb	Date
A	1000		12/12/2020
B	20000		1/11/2020
B		20000	1/11/2020
C	550		3/10/2020
D		6000	4/10/2020
E		5000	22/9/2020
E	5000		7/9/2020

eg: From the table above "E" also has the same transaction pattern as "B", but here condition for E should not be True() as the transactions are taking place on different dates.

And B should be True() as it is taking place on the same date.

Please help resolve this and do let me know if you need further information.

Regards,

Mintu Baruah

[selimovd]

Hey @MintuBaruah ,

that's exactly what the measure is doing:

That's the reason I ask you what exactly is not working?

Best regards

Denis