Amend Measure to work on id based on letters and numbers

[Invesco]

Hi Experts

See previous question with sample data 

https://community.fabric.microsoft.com/t5/Desktop/Identify-System-Approved-data-point-based-on-clien... 

How would you amend the calulcated column measure to work using an ID column that has both numbers and letters ID column is

gjdfg546dg5653sfsf format

Calculated Column Measure

IF (
    'Table'[Status] = "System Approved",
    IF (
        COUNTROWS (
            FILTER (
                'Table',
                'Table'[ID] = EARLIER('Table'[ID]) &&
                'Table'[Status] = "Authorised"
            )
        ) > 0,
        1,
        0
    ),
   0
)

[Anonymous · Accepted solution]

Hi @Invesco ,

Didn't quite understand what you meant, I made simple samples and you can check the results as follows.

Best regards,
Community Support Team_ Scott Chang

[Anonymous]

Hi @Invesco ,

This computed column also applies to the mix of letters and numbers you mentioned, try to understand EARLIER(). As you said, it will group all “abc1” into one group for calculation, just like 1, 23, 34 in your sample data.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Invesco]

Hi Community Help - slightly confused - can you help please

[Anonymous]

Hi @Invesco ,

What are your expected results?

Best regards,
Community Support Team_ Scott Chang

[Invesco]

Hi Community the expected end result is the same. The question is how do i handle the numbers and letters in the ID column using EARLIER??

[Anonymous · Accepted solution]

Hi @Invesco ,

Didn't quite understand what you meant, I made simple samples and you can check the results as follows.

Best regards,
Community Support Team_ Scott Chang

[Invesco]

Hi Commnity - See the following

I am trying to identify all data points in my FACT Table where we have clients that have the same ID and have Status as Authorised and System Apporved, so ID 1 has Status of Authorised and System Apporved in new column add 1 against System Apporved as shown else 0. Likewise ID 23. 

ID	Status	Flag
1asa	Authorised	0
1asa	System Approved	1
sdas23	Authorised	0
sdas23	System Approved	1
34	Authorised	0
34	Pipeline	0
45	Rejected	0
64	Pipeline	0
56	Pipeline	0

[Anonymous · Accepted solution]

Hi @Invesco ,

This seems to be two kinds of logic and has nothing to do with your ID, check the results below:

Check = IF([Status]="Authorised",1,0)

Measure = var _t = ADDCOLUMNS('Table',"Check2",COUNTAX(FILTER(ALL('Table'),[ID]=EARLIER([ID])&&[Status]="System Approved"),[ID]))
RETURN MAXX(FILTER(_t,[Check]=0),[Check2])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Invesco]

Hi impossible the IDs are a mixture of both.

[Anonymous]

just make sure the column data type is text not numeric