Problem in ContainsStringExact function. It does not compare exact value.

[Vinothsusai]

Hi,

I am new into power BI. I have formula like 11 + 12 . i just need to sum values of 11th id amount and 12th id amount values as below screen shot. But it retreives 1th id amount value. Please advise. In the scrrenshot I explained clearly. Please advise.

Thanks

Vinoth SUSAINATHAN

[v-juanli-msft]

Hi @Vinothsusai 

Do you have a dataset as below?

"Formula" ,"Total amount" and "amount" in your pictures are created by you based on the dataset, right?

line id	main amount
1	1
2
3	2
4	3
5
6
7	6
8	7
9
10	8
11	9
12

Could you give the calculation rule for "Formula" ,"Total amount" and "amount"?

You could add expected values in my simple data example above and share here.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vinothsusai]

Hi,

I am expecting the output like below. Please help me

Data

LineID	Amount	Formula	Total Amount
1	100		?
2	50	D1+D2	?
3	200	D1+D3	?
4	70	D2+D4	?
5	20	D1+D2+D4	?
6	10	D4+D6	?
7	20	D5+D6+D7	?
10	110	D5+D6+D7	?
11	130	D10+D11	?

When i use my measure,

Measure Formula = VAR maxf =

MAX ( HANGeneralJournals_Remodify[ReportLayout.Formula])

RETURN

IF ( maxf = BLANK () ,SUM(HANGeneralJournals_Remodify[Custom Main Amount]), CALCULATE(SUM(HANGeneralJournals_Remodify[Custom Main Amount]),FILTER(ALL(HANGeneralJournals_Remodify),ISBLANK([ReportLayout.Line ID])=FALSE()&&CONTAINSSTRINGEXACT(maxf,"D"&[ReportLayout.Line ID]))))

I got the wrong result for the formula (D10 + D11) in the row Line ID 11, the measure taken D1+D1 instead of D10 + D11. Please see the below screen shot.

LineID	Amount	Formula	Total Amount
1	100		100
2	50	D1+D2	150
3	200	D1+D3	300
4	70	D2+D4	120
5	20	D1+D2+D4	220
6	10	D4+D6	80
7	20	D5+D6+D7	50
10	110	D5+D6+D7	50
11	130	D10+D11	200

Instead of get 240 (D10+D11), I am getting 200(D1+D1) in lineitemID 11 by using my measure.

Please advise.

[v-juanli-msft · Accepted solution]

Hi @Vinothsusai 

Create a column

formula_alter = IF([Formula]<>BLANK(),[Formula]&"+")

Then create a measure

Measure 2 =
VAR MAXF =
    MAX ( Table1[formula_alter] )
RETURN
    IF (
        MAXF = BLANK (),
        SUM ( Table1[Amount] ),
        CALCULATE (
            SUM ( Table1[Amount] ),
            FILTER ( ALL ( Table1 ), CONTAINSSTRINGEXACT ( MAXF, "D" & [LineID] & "+" ) )
        )
    )

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vinothsusai]

Hi,

Thank you for your reply.

Yes. I have a dataset as mentioned below.

Line ID  GroupAccount  GroupAccountDescription  MainAccount  ReportLayout.Formula  Amount  TotalAmount

1	R701000	Gross sales	70100000		1925702
3	R708001	Commissions to retail partner	65100000	D3	1952934
35	R641001	Chef	64110001		-75.2
36	R641002	Assistant	64110002	D3+D35+D36	111478
40	R645001	Chef	64510001	D1+D36	-1698.72
41	R645002	Assistant	64510002		21069.99
46	R695000	Subsidies	64910000		62371.96
47	R621000	Temporary worker	62110000	D40+D47	1920000
48	R647000	Medical fees	64750000	D41+D47+D48	7595.4
50	R615001	Cleaning Products	60222000		499
55	R615005	Maintenance contract (preventive)	61560000		140157.9
60	R623003	Other marketing PoS	62310000	D41+D47+D48+D60	7686.54
62	R616000	Insurance	61610000		13146.64
63	R622001	Controls	62260005	D60+D62	1060
68	R613001	Lease expense - External party	61320001		35624.28
136	R623530	Agency Compensation	62300000	D68+D136	88372.66
137	R623540	Catalogues / Brochures	62360000		180617.9
139	R628500	Others	62310000	D137+D139	7686.54
142	R622620	Audit fees	62260003		2700
155	R622820	Official Acts	62270001		23195.7
164	R613100	Lease expense	61320001		35624.28

Here i need to find Total amount based on the formula.

@v-lili6-msfthad already provided a solution for me. Please see the below ticket url. In the formula I have a problem in ContainsStringExact function

https://community.powerbi.com/t5/Desktop/Calculate-formula-column-based-on-Id-referred-column-Non/m-...

Fornula:

Measure Formula = VAR maxf =
MAX ( HANGeneralJournals_Remodify[ReportLayout.Formula])

RETURN
IF ( maxf = BLANK () ,SUM(HANGeneralJournals_Remodify[Custom Main Amount]), CALCULATE(SUM(HANGeneralJournals_Remodify[Custom Main Amount]),FILTER(ALL(HANGeneralJournals_Remodify),ISBLANK([ReportLayout.Line ID])=FALSE()&&CONTAINSSTRINGEXACT(maxf,"D"&([ReportLayout.Line ID])))))

 Formula field is a string type. While extract the line ID from the formula (for ex: D11 + D12 = 11 + 12. ID 11th Amount value is 0 and ID 12th Amount value is 0 so the total is 0 but the result seems wrong as retrieved ID 1th Amount Value because I am using ContainsStringExact function when it compare D11, it takes as D1 . D1 string with in D11)

To resolve this, I have used another formula. But it works serial IDs for ex: D1+D2+D3+D4+D5 but not in random Ids like D5+D7+ D9+D20 because the formula is looping serially.

Measure Formula = VAR maxf =

    MAX ( HANGeneralJournals_Remodify[ReportLayout.Formula] )

VAR sub = SUBSTITUTE ( maxf, "D", "" )

VAR no =

    LEN (maxf) - LEN ( SUBSTITUTE ( sub, "+", "" ) )

VAR a =

    CALCULATE (

        SUM ( HANGeneralJournals_Remodify[Custom Main Amount]  ),

        FILTER (

            ALL ( HANGeneralJournals_Remodify ),

            HANGeneralJournals_Remodify[ReportLayout.Line ID] <= MAX ( HANGeneralJournals_Remodify[ReportLayout.Line ID]  )

                && HANGeneralJournals_Remodify[ReportLayout.Line ID]

                    >= MAX ( HANGeneralJournals_Remodify[ReportLayout.Line ID]  ) - no

        )

    )

RETURN

    IF ( maxf = BLANK (),SUM (HANGeneralJournals_Remodify[Custom Main Amount]) , a )

Please advise

Thanks

Vinoth S