topic Re: problem with Dax measure in DAX Commands and Tips

problem with Dax measure

ania_roh — Wed, 11 Jan 2023 09:57:52 GMT

Hello,

I need some help in building my measure because I can´t find a way. I would be very grateful if you could help me.

So,

I have three columns (in the same table): column of localization_destino_original, column of localization_destino and number:

And now, I need to build a measure which would have two parts ( one of the localization_destino and the other localization_destino_original) but we would use it in visualization filtred only by localization_destino_original, like that:

Now, the measure would have two parts: Let´s say X and Y.

Measure = X + Y

So the X would be the like that (and it works):

X= SUMX(

FILTER(

Table,

Table[LOCALIZACION_DESTINO] <> ANALISIS_ALBARAN2[LOCALIZACION_DESTINO_ORIGINAL]),

Number))

How can I calculate my Y, if I want to say it all around?

I will explain it with an example:

I would be very appreciated if you help me.

Thank you a lot.

Re: problem with Dax measure

Mahesh0016 — Wed, 11 Jan 2023 11:17:00 GMT

ania_roh Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: problem with Dax measure

ania_roh — Wed, 11 Jan 2023 12:04:22 GMT

Yes, Mahesh0016, thank you.
I created powerbi sample but I don´t find a way to attach it here.
The table would be like that : (localizacion_destino, localización_destino_original, value)

1111	1111	1
2343	1111	2
2345	2345	3
7642	3456	4
3456	3456	5
1111	2343	6
2343	1111	7
7642	7642	8
1111	2345	9
7642	4582	10

The measure X would be like that:

The visualization is like that:

Now I need calculate Y (the next column in the visualization) and I don´t know how.
For this example, for ID_destino = 1111, Y should give 15:

Thank you a lot

Re: problem with Dax measure

Mahesh0016 — Wed, 11 Jan 2023 12:40:47 GMT

ania_roh

Y =

SUMX (

FILTER (

localizationTable,

localizationTable[localizacion_destino] = 1111

&& localizationTable[localización_destino_original] = 1111

localizationTable[value]

)

****************************************************************************************

_Y =

SUMX (

FILTER (

localizationTable,

localizationTable[localizacion_destino] = 1111

localizationTable[value]

)

ania_roh If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.

Re: problem with Dax measure

ania_roh — Wed, 11 Jan 2023 12:47:56 GMT

Yes Mahesh0016 but I want to have it for all rows of that visualizations.

Not only for 1111 (I put it to give an example).

Re: problem with Dax measure

FreemanZ — Wed, 11 Jan 2023 15:49:06 GMT

hi ania_roh

Not sure if i fully get your, try to write the measure like this:

Y = VAR _dest = MAX(TableName[DestOrig]) RETURN SUMX( FILTER( ALL(TableName), TableName[Dest] = _dest &&TableName[DestOrig] <> _dest ), TableName[Value] )

i tried and it worked like this:

Re: problem with Dax measure

ania_roh — Thu, 12 Jan 2023 11:07:09 GMT

Ohhh I can´t believe it! Freeman

Thank you a lot

What if I want it to responde to other fields of the table? Like date and section, for example?

Thank you

Re: problem with Dax measure

FreemanZ — Thu, 12 Jan 2023 11:47:40 GMT

hi ania_roh

that was something never mentioned, try like

Y =
VAR _dest = MAX(TableName[DestOrig])
RETURN
SUMX(
FILTER(
ALL(TableName[Dest], TableName[DestOrig] ),
TableName[Dest] = _dest
&&TableName[DestOrig] <> _dest
),
TableName[Value]
)

Re: problem with Dax measure

ania_roh — Thu, 12 Jan 2023 15:38:49 GMT

FreemanZ no, it doesn´t work 😞

Re: problem with Dax measure

FreemanZ — Fri, 13 Jan 2023 03:05:26 GMT

hi ania_roh

try like:

Y2 = VAR _dest = MAX(TableName[DestOrig]) RETURN CALCULATE( SUM(TableName[Value]), TableName[Dest] = _dest, TableName[DestOrig] <> _dest )

this one takes filters on other column, like:

the sample dataset:

SectionDestDestOrigValue

A	1111	1111	1
B	1111	2343	6
C	1111	2345	9
A	2343	1111	2
B	2343	1111	7
C	2345	2345	3
A	3456	3456	5
A	7642	3456	4
B	7642	7642	8
C	7642	4582	10

Re: problem with Dax measure

reginaldrucker — Sat, 21 Jan 2023 01:17:21 GMT

yeah this will explain everything baby or my dear lady