topic Re: Transformed Table Measure in DAX Commands and Tips

Transformed Table Measure

Anonymous — Thu, 17 Oct 2019 04:10:12 GMT

Hi Expert,

I would like to have 2 measures that to transform table

Raw data is like this

Customer	Code	Item	Amount
A	01	MM	100
A	01	NN	200
A	02	ZZ	300
B	01	MM	400
B	01	NN	500
B	02	ZZ	600
B	02	MM	650
C	01	MM	700
C	01	NN	800
C	01	ZZ	900
D	02	MM	1000
D	02	NN	1100
D	02	ZZ	1200
E	02	MM	1300
E	02	NN	1400
E	02	ZZ	1500

Measure 1: Sum of 01
Measure 2: Sum of 02 (based on the customer have code "01")

to get the expected result as this

	Sum of 01	Sum of 02
MM	1200 (100+400+700)	650
NN	1500 (200+500+800)	0
ZZ	900	900 (300+600)

Many thanks

Simon

Re: Transformed Table Measure

Nathaniel_C — Thu, 17 Oct 2019 04:01:33 GMT

Hi Anonymous ,

Here is the first part. Not sure what you want for the second part. Will look at it again.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Sum of 1 = 
var _code = 1
var _calc = CALCULATE([Sum of Amount],'Count'[Code]=1)


return _calc

Re: Transformed Table Measure

Nathaniel_C — Thu, 17 Oct 2019 04:06:00 GMT

Hey Anonymous ,

Shouldn't it be A2ZZ = 300

B2ZZ = 600 and B2MM = 650?
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: Transformed Table Measure

Anonymous — Thu, 17 Oct 2019 04:22:42 GMT

Oh, sorry

Sum of ZZ+02 should be (300+600)=900

The table is amended

Thanks for correction

Re: Transformed Table Measure

Nathaniel_C — Thu, 17 Oct 2019 04:22:04 GMT

Anonymous ,

Ok then! Added a column to check on whether the customer had 1. Here is my pbix. Count of 1 and 2

Sum of 2 = 

var _code = 2
var _calc = CALCULATE([Sum of Amount],'Count'[Code]=2,'Count'[IF Customer has 1]>=1)


return _calc

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: Transformed Table Measure

Nathaniel_C — Thu, 17 Oct 2019 04:25:17 GMT

Hi Anonymous ,

Calculated column is

IF Customer has 1 = CALCULATE(COUNTROWS('Count'),FILTER(ALLEXCEPT('Count','Count'[Customer]),'Count'[Code]=1))

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: Transformed Table Measure

Anonymous — Thu, 17 Oct 2019 04:25:43 GMT

Hi Nathaniel_C , It's help, Great thanks!

Re: Transformed Table Measure

Anonymous — Thu, 17 Oct 2019 04:48:59 GMT

May I ask further, It is possible that not create a column to do so?

Re: Transformed Table Measure

Nathaniel_C — Thu, 17 Oct 2019 21:40:44 GMT

Hi Anonymous ,

This is the new Sum of 2, which does not need the Calculated Column. Had to go to another guy on the leaderboards jdbuchanan71 .

Whew!

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Sum of 2 =
VAR _CustList =
    CALCULATETABLE (
        INTERSECT (
            CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1 ),
            CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 2 )
        ),
        ALLEXCEPT ( 'Count', 'Count'[Customer] )
    )
RETURN
    CALCULATE (
        SUM ( 'Count'[Amount] ),
        'Count'[Customer] IN ( _CustList ),
        KEEPFILTERS ( 'Count'[Code] = 2 )
    )

Re: Transformed Table Measure

Anonymous — Fri, 18 Oct 2019 01:37:56 GMT

Perfect, exactly what I want, thanks so much

Re: Transformed Table Measure

Anonymous — Fri, 18 Oct 2019 07:07:59 GMT

Hi Nathaniel_C

This is equation I modified.

Sum of 2 (New) = 
VAR _CustList =
    CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1 )
RETURN
    CALCULATE ( 
        SUM ( 'Count'[Amount] ),
        'Count'[Customer] IN ( _CustList ),
        'Count'[Code] = 2)

I'm so curious why it's empty for (ZZ, Sum of 2 (New) ) and the total is correct, it's very very strange. how come?

Re: Transformed Table Measure

Nathaniel_C — Fri, 18 Oct 2019 14:27:37 GMT

Hi Anonymous ,

We ran into the same thing.

Here is the first solution and the subsequent solution that jdbuchanan71 sent. The first one had exactly the same issue. Not sure why.

First one:
Measure = 
VAR _CustList = 
    INTERSECT (
        CALCULATETABLE (
            DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1),
        CALCULATETABLE (
            DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 2)
    )
RETURN
    CALCULATE ( 
        SUM ( 'Count'[Amount] ),
        'Count'[Customer] IN ( _CustList ),
        KEEPFILTERS( 'Count'[Code] = 2 )
    ) 

Second one:
Measure = 
VAR _CustList = 
CALCULATETABLE(
    INTERSECT (
        CALCULATETABLE (
            DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1),
        CALCULATETABLE (
            DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 2)
    )
    ,ALLEXCEPT('Count','Count'[Customer])
)
RETURN
    CALCULATE ( 
        SUM ( 'Count'[Amount] ),
        'Count'[Customer] IN ( _CustList )
        ,KEEPFILTERS( 'Count'[Code] = 2 )
    )

Nathaniel

Re: Transformed Table Measure

jdbuchanan71 — Fri, 18 Oct 2019 16:00:24 GMT

Like all measures that act differently than we think it should it's all about filter context. If we take the line for Item NN as an example:
The first part of your measure

VAR _CustList =
    CALCULATETABLE ( DISTINCT ( 'Count'[Customer] ), 'Count'[Code] = 1 )

gets the filter context of [Item] = NN (from the line in the visual) and you add the filter [Code] = 1. This returns a list of customers, in this case A, B, C (those are the only customers with [Item] NN and [Code] 1.

Then the second line

    CALCULATE ( 
        SUM ( 'Count'[Amount] ),
        'Count'[Customer] IN ( _CustList ),
        'Count'[Code] = 2)

Calculates the SUM and applies 3 filters.
1. The [Item] filter from the visual of NN
2. The list from VAR _CustList: (A, B, C)
3. [Code] = 2 which only appears on lines for customers (D, E)

So you are asking it to SUM ( 'Count'[Amount] ) for customers (A, B, C) from these lines:

Customer	Code	Item	Amount
E	2	NN	1400
D	2	NN	1100

No lines match all the criteria so we get a blank.