topic Re: Measure that condtional in DAX Commands and Tips

Measure that condtional

Anonymous — Thu, 17 Oct 2019 05:29:29 GMT

Hi Expert,

I would like to have a measure,

Example:

User	Code	Region	Value
A	A1	UK	1
A	A1	US	2
A	A2	DE	3
B	A1	UK	4
B	A1	US	5
B	A2	DE	6
B	A2	UK	7
C	A1	UK	8
C	A1	US	9
C	A1	DE	10
D	A2	UK	11
D	A2	US	12
D	A2	DE	13

(conditional that can't create column)

Expected measure: [Sum of A2] for whose user have Code "A1", so that result

Region	Sum of A2
UK	7
DE	9
total	16

Many thanks,

Simon

Re: Measure that condtional

AlB — Thu, 17 Oct 2019 06:50:08 GMT

Hi Anonymous

Your result doesn't seem to match what you describe. In any case, try this:

1. Place Region in the rows of a table visual

2. Place Code in a slicer and select A1

3. Place this measure in the table visual

Measure = SUM( Table1[Value] )

4. You can add more slicers if you want to refine the sum further

Re: Measure that condtional

Anonymous — Thu, 17 Oct 2019 07:55:39 GMT

AlB
Thanks for your reply, however that's not my expected answer.
I've checked my expected result is corrrect,
Please see the picture to be clear.

Raw table

Expected measure: [Sum of A2] for those user have "A1"

Many thanks

Re: Measure that condtional

AlB — Thu, 17 Oct 2019 10:28:01 GMT

Ok, but then you'd need to explain better what you need. You are leaving user D out of the calculation. Is that what you want? If so, you just have to include another slicer for User and select users A, B, C only, leaving D out

Another option would be not to use this User slicer and embed the filtering in the measure

MeasureV2 =
CALCULATE ( SUM ( Table1[Value] ), Table1[User] <> "D" )

The first option is probably more versatile, since you can select the users of interest on the fly

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

Re: Measure that condtional

Anonymous — Fri, 18 Oct 2019 08:37:37 GMT

AlB Thanks so much for your afford.

however it's not my expected answer as well

" You are leaving user D out of the calculation. Is that what you want?" - correct, but this should be done by calculation and not hard coding. Since user D doesn't have code "A1", so D have to be filtered out.

To add Slicer is not my requirement as well.

Re: Measure that condtional

AlB — Fri, 18 Oct 2019 15:15:17 GMT

1. Place Region in the rows of a table visual

2. Place this measure in the table visual

Measure-Sum of A2 =
SUMX (
    Table1;
    IF (
        Table1[Code] = "A2"
            && "A1" IN CALCULATETABLE ( DISTINCT ( Table1[Code] ); ALLEXCEPT ( Table1; Table1[User] ) );
        Table1[Value];
        0
    )
)

See it all at work in the attached file.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

Re: Measure that condtional

Anonymous — Wed, 23 Oct 2019 04:15:31 GMT

AlBMany thanks

I'm wondering that can your code here vary with the slicer changing?

 "A1" IN CALCULATETABLE ( DISTINCT ( Table1[Code] ); ALLEXCEPT ( Table1; Table1[User] ) )

It's expected dynamical

Re: Measure that condtional

AlB — Wed, 23 Oct 2019 22:00:27 GMT

Anonymous

I'd need more details about what you exactly need. An example would be of great help