DAX too complicated for me

[PVO1]

Good day,

Looking for your help in creating a measure (not a column). Can't seem to figure it out at all.

This measure should do the 3 things listed below. I would like to use this in a measure together with the [ID] column in a visual.

- IF [Amount] = 0 then BLANK

- IF [N/W] = "W" then BLANK

- IF [Connection] = BLANK then [Amount] else (not sure how to explain, see below) CALCULATE(SUM([Amount]),ALL([Connection]))

Data is shown below with the expected result of the measure added. In my visual I would like to only use ID & Measure.

ID	N/W	Amount	Connection	Measure
1	N	100		100
2	N	0		BLANK
3	N	200		200
4	N	300	a	150
5	W	-150	a	BLANK
6	N	400	b	390
7	W	80	b	BLANK
8	W	-90	b	BLANK

[PVO1]

@Pragati11 & @amitchandak 

Thanks a lot! Both of your calculations gave me the correct result. Unfortunatly I think I was not clear about the desired result. I would like to use this result in a table visual with only the ID & the measure.

So the visual would look like this:

ID   Measure

1     100

3     200

4     150

6     390

But perhapse I can figure this out on my own, with your input.

[Pragati11 · Accepted solution]

Hi @PVO1 ,

A quick workaround for this can be having the following in the table visual:

Just hide the CONNECTION column in the table visual by adjusting the width of this column in table visual. 

Make sure to do the following before hiding the column:

Rename CONNECTION column to something like single character. I have renamed it to a dot "."

Now decrease the width of this column:

Hope this workaround helps.

Thanks,

Pragati

[Pragati11]

HI @PVO1 ,

Can you please mark the other solutions with the DAX measure as the SOLUTION to this thread?
That is the actual soution to the query you asked. And the solution which you marked is an add-on solution to the issue.

Kindly mark the other one also as the solution to this thread.

Thanks,

Pragati

[PVO1]

Thanks again!

Just spent a day trying to figure this out, to come to the conclusion that it's not possible with a measure. Think I'm going to add some columns to the dataset in order to create the desired result. 

[Pragati11]

Hi @PVO1 ,

You can write the following measure to achieve the result:

Adding the measure calculation as follows:

MeasureCalc =

var t1 = SELECTEDVALUE('Table (3)'[Amount])

var t2 = SELECTEDVALUE('Table (3)'[N/W])

var t3 = SELECTEDVALUE('Table (3)'[Connection])

var t4 = CALCULATE(SUM('Table (3)'[Amount]), ALLEXCEPT('Table (3)', 'Table (3)'[Connection]))

RETURN

SWITCH(

TRUE(),

t1 = 0, BLANK(),

t2 = "W", BLANK(),

t3 = BLANK(), t1, t4

)

Thanks,

Pragati

[amitchandak]

@PVO1 , try like

Measure =
var _1 = calculate(sum(Table[Amount]), filter(Table, Table[Amount] <>0 && [N/W] <> "W" ))
var _2 = calculate(_1, ALL(Table[Connection])
return
if(isblank(Max(Table[Connection])),_1, _2)