DAX measure returns concatenated result that meets two conditions

[JulieB_]

Hi all, 

I have to create a Matrix where articles are returned if they meet two conditions. A specific text must be returned with the help of concatenatex. 

Here a pbix file with the test data where I did some try outs. File available via WeTransfer: https://we.tl/t-MwcC3sVEvj 

I need to display the articles that meet below two conditions in a Matrix:

• The sum 'Amount' is greater than the selected value in the slicer. 
  • I created a parameter value that is added in a slicer. 
  • If an article appears several times in the selected period under the same 'Profit center' and 'Reason' then we look at the sum amount. (example below: Article 'pomme' in Profit Center 'A')
• The Article is equal to a specific 'Reason':
  • DAX measure 1: where article = Damaged
  • DAX measure 2: where article = Foodbank || Giveaway || Expired 

Test data: 

ID	Article	Reason	Amount	Date	Profit Center
1	pomme	Damaged	2000	44812	A
1	pomme	Damaged	500	44813	A
1	pomme	Damaged	200	44812	B
4	poire	Damaged	1500	44813	A
5	banane	Expired	3000	44814	A
6	paprika	Expired	800	44815	A
13	chemise	Expired	100	44822	B
17	chat	Expired	600	44826	C
7	orange	Foodbank	1500	44816	B
7	orange	Foodbank	500	44817	B
10	pain	Damaged	600	44819	B
8	choco	Foodbank	400	44817	B
12	pantalon	Damaged	700	44821	B
9	moutarde	Foodbank	300	44818	B
14	dentifrise	Foodbank	50	44823	B
11	pasta	Giveaway	1800	44820	B
15	bain	Giveaway	60	44824	B
16	chien	Giveaway	70	44825	C

Expected Result when slicer is set on 1000 (display articles > 1000):

Profit Center	DAX measure 1: Amount Damaged	DAX measure 2: Amount Expired, Foodbank, Giveaway
A	Damaged: 2500 - pomme - 1 1500 - poire - 4	Expired: 3000 - banane - 5
B		Foodbank: 2000 - orange - 7 Giveaway: 1800 - pasta - 11

Expected result when you drill down on Profit Center:

Profit Center	DAX measure 1: Amount Damaged	DAX measure 2: Amount Expired, Foodbank, Giveaway
A
1	Damaged: 2500 - pomme - 1
2	Damaged: 1500 - poire - 1
3		Expired: 3000 - banane - 5
B
1		Foodbank: 2000 - orange - 7
2		Giveaway: 1800 - pasta - 11

Thank you!

@tamerj1 can you maybe help? 🙂

[tamerj1 · Accepted solution]

Good morning @JulieB_ 
The first blank line issue is fixed in the following code. Please refer to attached sample file.

Amount Damaged = 
VAR ValueParameter =
    SELECTEDVALUE ( 'testdata'[Amount] )
VAR T1 =
    FILTER ( testdata, testdata[Reason] = "Damaged" )
VAR T2 =
    SUMMARIZE (
        T1,
        testdata[Article],
        testdata[ID],
        "@Amount", SUM ( testdata[Amount] )
    )
VAR T3 =
    FILTER ( T2, [@Amount] > ValueParameter )
VAR Result =
    CONCATENATEX (
        T3,
        [@Amount] & " - " & [Article] & " - " & [ID],
        UNICHAR ( 10 ),
        [@Amount], DESC
    )
RETURN
    IF (
        NOT ISEMPTY ( T3 ) && HASONEVALUE ( testdata[Profit Center] ),
        "Damaged:" & UNICHAR ( 10 ) & Result
    )

Amount ExpiredFoodbankGiveaway_test2 = 
VAR T1 =
    FILTER ( testdata, testdata[Reason] IN { "Expired", "Foodbank", "Giveaway" } )
VAR T2 =
    SUMMARIZE (
        T1,
        testdata[Article],
        testdata[ID],
        testdata[Reason],
        "@amount", SUM ( testdata[Amount] )
    )
VAR T3 =
    FILTER (
        T2,
        [@amount] < [Selected Neg target]
            || [@amount] > [Selected Pos target]
    )
VAR T4 =
    SUMMARIZE (
        T3,
        testdata[Reason],
        "@Concatenation",
            VAR CurrentReason = testdata[Reason]
            VAR T5 =
                FILTER ( T3, testdata[Reason] = CurrentReason )
            RETURN
                CONCATENATEX (
                    T5,
                    [@amount] & " - " & testdata[Article] & " - " & testdata[ID],
                    UNICHAR ( 10 ),
                    [@amount], DESC
                )
    )
VAR Result =
    CONCATENATEX (
        T4,
        testdata[Reason] & ":" & UNICHAR ( 10 )
            & [@Concatenation] & UNICHAR ( 10 )
    )
RETURN
    IF ( NOT ( ISEMPTY ( T3 ) ) && HASONEVALUE ( testdata[Profit Center] ), Result )

[JulieB_]

Hi @tamerj1 problem solved!

Thanks a lot to help out with your DAX expertise! 

I learned something new 👌

[tamerj1]

Hi @JulieB_ 

sorry I could not test the formula as I'm not on my PC, so you might find some errors but I believe this is illustrates the general idea of how to achieve this. 

Amount Damaged =
VAR ValueParameter =
    SELECTEDVALUE ( 'Parameter'[Value] )
VAR T1 =
    FILTER ( TableName, TableName[Reason] = "Damaged" )
VAR T2 =
    SUMMARIZE (
        T1,
        TableName[Article],
        TableName[ID],
        "@Amount", SUM ( TableName[Amount] )
    )
VAR T3 =
    FILTER ( T2, [@Amount] > ValueParameter )
VAR Result =
    CONCATENATEX (
        T4,
        [@Amont] & " - " & [Article] & " - " & [ID],
        UNICHAR ( 10 ),
        [@Amont], ASC
    )
RETURN
    IF (
        NOT ISEMPTY ( T3 ) && HASONEVALUE ( TableName[Profit Center] ),
        "Damaged:" & UNICHAR ( 10 ) & Result
    )

[JulieB_]

Hi @tamerj1 ,

Thanks for the fast reply!

The code seems to work fine when T1 is equal to one Reason. 

But when T1 is equal to several Reasons the RETURN step is not working because there are several reasons possible.

VAR T1 = FILTER(testdata, testdata[Reason] = "Expired" || testdata[Reason] = "Foodbank" || testdata[Reason] = "Giveaway")

I tried to edit the code as following but it is not working.

Amount ExpiredFoodbankGiveaway_test2 = 
VAR T1 = FILTER(testdata, testdata[Reason] = "Expired" || testdata[Reason] = "Foodbank" || testdata[Reason] = "Giveaway")

VAR T2 = 
SUMMARIZE(
    T1,
    testdata[Article],
    testdata[ID],
    testdata[Reason],
    "@amount", SUM(testdata[Amount])
)

VAR T3 = FILTER(T2, [@amount] < [Selected Neg target] || [@amount] > [Selected Pos target])

VAR Result1 = 
CALCULATE(
    CONCATENATEX(
        T3, 
        [@amount] & " - " & testdata[Article] & " - " & testdata[ID],
        UNICHAR(10),
        [@amount], DESC
    )
)

VAR Result2 = 
CONCATENATEX (
    VALUES(testdata[Reason]),
    testdata[Reason] & UNICHAR ( 10 ) & Result1 & UNICHAR ( 10 ), 
    UNICHAR ( 10 ), 
    testdata[Reason], ASC 
)
RETURN
IF(
    NOT(ISEMPTY(T3)) && HASONEVALUE(testdata[Profit Center]),
    Result2
)

 Gives me this, but what I need is the red:

[tamerj1]

Hi @JulieB_ 

yes that was for one Reason.
I don't think Result2 is required. Please try

Amount ExpiredFoodbankGiveaway_test2 =
VAR T1 =
    FILTER ( testdata, testdata[Reason] IN { "Expired", "Foodbank", "Giveaway" } )
VAR T2 =
    SUMMARIZE (
        T1,
        testdata[Article],
        testdata[ID],
        testdata[Reason],
        "@amount", SUM ( testdata[Amount] )
    )
VAR T3 =
    FILTER (
        T2,
        [@amount] < [Selected Neg target]
            || [@amount] > [Selected Pos target]
    )
VAR Result =
    CONCATENATEX (
        T3,
        testdata[Reson] & ":"
            & UNICHAR ( 10 ) & [@amount] & " - " & testdata[Article] & " - " & testdata[ID],
        UNICHAR ( 10 ),
        [@amount], DESC
    )
RETURN
    IF ( NOT ( ISEMPTY ( T3 ) ) && HASONEVALUE ( testdata[Profit Center] ), Result )

[JulieB_]

Hi @tamerj1 ,

I tried to use VAR Result2 to avoid repetition of the Reason in the result.

Result of your code: 

The AmountDamaged DAX measure does not repeat the Reason.

See Damaged example above with two articles inthere. That is the desired display.

The AmountExpiredFoodbankGiveaway DAX measure does repeat the Reason. See Foodbank example above. 

I thought that with a double Concatenatex that might be avoided? But my attemps to write that code failed ^^

You have a solution? 

Thanks for helping out!

[tamerj1]

@JulieB_ 

I'm on the phone trying to imagine how would that data look like, so please bare with me. Please try it this way

Amount ExpiredFoodbankGiveaway_test2 =
VAR T1 =
    FILTER ( testdata, testdata[Reason] IN { "Expired", "Foodbank", "Giveaway" } )
VAR T2 =
    SUMMARIZE (
        T1,
        testdata[Article],
        testdata[ID],
        testdata[Reason],
        "@amount", SUM ( testdata[Amount] )
    )
VAR T3 =
    FILTER (
        T2,
        [@amount] < [Selected Neg target]
            || [@amount] > [Selected Pos target]
    )
VAR T4 =
    SUMMARIZE (
        T3,
        testdata[Reson],
        "@Concatenation",
            CONCATENATEX (
                T3,
                [@amount] & " - " & testdata[Article] & " - " & testdata[ID],
                UNICHAR ( 10 ),
                [@amount], DESC
            )
    )
VAR Result =
    CONCATENATEX ( T4, testdata[Reson] & ":" & UNICHAR ( 10 ) & [@Concatenation] )
RETURN
    IF ( NOT ( ISEMPTY ( T3 ) ) && HASONEVALUE ( testdata[Profit Center] ), Result )

[JulieB_]

@tamerj1 Thats not working neither 😋

Not bad however trying to solve this only with your phone! 

[tamerj1]

@JulieB_ 

If this didn't work, please leave for tomorrow. I will have to download your file work on it. 

Amount ExpiredFoodbankGiveaway_test2 =
VAR T1 =
    FILTER ( testdata, testdata[Reason] IN { "Expired", "Foodbank", "Giveaway" } )
VAR T2 =
    SUMMARIZE (
        T1,
        testdata[Article],
        testdata[ID],
        testdata[Reason],
        "@amount", SUM ( testdata[Amount] )
    )
VAR T3 =
    FILTER (
        T2,
        [@amount] < [Selected Neg target]
            || [@amount] > [Selected Pos target]
    )
VAR T4 =
    SUMMARIZE (
        T3,
        testdata[Reson],
        "@Concatenation",
            VAR CurrentReason = testdata[Reson]
            VAR T5 =
                FILTER ( T3, testdata[Reson] = CurrentReason )
            RETURN
                CONCATENATEX (
                    T5,
                    [@amount] & " - " & testdata[Article] & " - " & testdata[ID],
                    UNICHAR ( 10 ),
                    [@amount], ASC
                )
    )
VAR Result =
    CONCATENATEX (
        T4,
        UNICHAR ( 10 ) & testdata[Reson] & ":"
            & UNICHAR ( 10 ) & [@Concatenation]
    )
RETURN
    IF ( NOT ( ISEMPTY ( T3 ) ) && HASONEVALUE ( testdata[Profit Center] ), Result )

[JulieB_]

@tamerj1 yes that returns almost the good result ! 😄

This DAX measure is getting long haha ^^

Maybe a detail but the firt line of the field returns a white space... 

In VAR Result there is a UNICHAR(10) that starts the expression of the concatenatex. Thanks to that, if there is a second Reason it will come on the next line as desired. But the first reason is therefore also starting on the next line. Would be nice if the text could start on the first line 😇

Indeed lets have a fresh look tomorrow 🙂 Thank you!