Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
lucas_canova
Helper I
Helper I

Measure doesn't working with selectedvalue

Hi, i need help!

 

I have the follow measure:

 

REAL = CALCULATE( 
   sum(BA_DIARIO[VLR_LANCAMENTO])/1000000,
    LASTDATE(BA_DIARIO[DT_EXEC])
    )

 

 This one works fine and do what i need properly.

 

The issue comes now. I try to use them in the code like bellow:

 

Sum Switch = 
SWITCH (
TRUE(),
SELECTEDVALUE ( BA_DIARIO[DC] ) = "△ REAL X BN", [REAL],
SUM ( BA_DIARIO[VLR_LANCAMENTO] )/1000000
)

 

 it aways returns the sum of else statement.

 

Yes, i tested swap that measure [REAL] in SELECTEDVALUE ( BA_DIARIO[DC] ) = "△ REAL X BN", [REAL] by 

SELECTEDVALUE ( BA_DIARIO[DC] ) = "△ REAL X BN", 0
 
And worked, returns 0 in "△ REAL X BN".
 
What i do?
1 ACCEPTED SOLUTION

@lucas_canova Ok, it's more clear. So try with create a new calculated table:

DC_TABLE = DISTINCT(BA_DIARIO[DC])
you have to relate this table with BA_DIARIO and use this new table as column in the matrix.
Then create another new table:
DT_TABLE = DISTINCT(BA_DIARIO[DT_EXEC])
but DON'T relate this new one with BA_DIARIO.
Then calculate measure like:
Sum Switch =
VAR BN6 = CALCULATE(SUM(BA_DIARIO[VLR_LANCAMENTO]) / 1000000, DC_TABLE[BA_DIARIO_DC] = "BN 6 + 6")
VAR MAXDTEXEC = MAX(DT_TABLE[DT_EXEC])
VAR DCMAXEXEC = CALCULATE(SUM(BA_DIARIO[VLR_LANCAMENTO]) /1000000, BA_DIARIO[DT_EXEC] = MAXDTEXEC, ALL(DC_TABLE))
VAR CALC   =
IF(
SELECTEDVALUE (DC_TABLE[BA_DIARIO_DC] ) = "△ REAL X BN", BN6 - DCMAXEXEC, SUM(BA_DIARIO[VLR_LANCAMENTO]) / 1000000)

RETURN CALC
 
and you achieve the output:
BeaBF_0-1722444765540.png

 

 

BBF

View solution in original post

16 REPLIES 16
BeaBF
Super User
Super User

@lucas_canova Hi!

 

Try with:

Sum Switch =
SWITCH (
TRUE(),
SELECTEDVALUE(BA_DIARIO[DC]) = "△ REAL X BN", CALCULATE([REAL]),
SUM(BA_DIARIO[VLR_LANCAMENTO])/1000000
)

 

By wrapping [REAL] in a CALCULATE function, you force its evaluation in the context of the SWITCH statement.

 

BBF

Returns blank too

@lucas_canova Can you share the pbix file? Meanwhile you can try:

 

REAL = CALCULATE(
SUM(BA_DIARIO[VLR_LANCAMENTO])/1000000,
LASTDATE(BA_DIARIO[DT_EXEC])
)

 

Sum Switch =
SWITCH (
TRUE(),
SELECTEDVALUE(BA_DIARIO[DC]) = "△ REAL X BN",
CALCULATE(
SUM(BA_DIARIO[VLR_LANCAMENTO])/1000000,
LASTDATE(BA_DIARIO[DT_EXEC])
),
SUM(BA_DIARIO[VLR_LANCAMENTO])/1000000
)

 

BBF

 

@BeaBF, follow pbix bellow:
https://drive.google.com/file/d/1J27PzB6UynHcecdeCpBeanFQth19igvc/view?usp=drive_link
In final stage, i need to perform delta between [REAL] - [BN] measures in that switch statement. I posted only [REAL] for more ez compreension .

@lucas_canova  please accept my request on drive.

@lucas_canova the reason of always blank is that there is no VLR_LANCAMENTO value for DC = △ REAL X BN. Let's see the table:

 

BeaBF_0-1722438444228.png

 

because of blank values in VLR_LANCAMENTO, also REAL is Blank.

 

BBF

@BeaBF , swap in measure SELECTEDVALUE(BA_DIARIO[DC]) = "△ REAL X BN", [REAL] by
SELECTEDVALUE(BA_DIARIO[DC]) = "△ REAL X BN", 0. U will see what i'm talking. My logical is working, but idk how to handle with this dax issue to perform [REAL] - [BN].

@BeaBF that is the reason i'm using a measure to switch the blank value for a calculated one. I did it on another pbix and worked.. idk what im doing different

@lucas_canova  can you explain me the desidered output for △ REAL X BN?

 

@BeaBF, I need the difference between last day, and bn 6 + 6, this diferrence need to born in "

△ REAL X BN".
In bellow, i have the 30 day in 202407, so the measure needs to perform delta with him and bn 6 + 6:
lucas_canova_0-1722442065967.png

First line for example the results must be -0.28

The measures [REAL] and [BN] perform this calculation. I just need [REAL] - [BN].

@lucas_canova Ok, so add "+0" in real formula:

 

BeaBF_0-1722442407117.png

 

BBF

@BeaBF I need this

lucas_canova_0-1722442572069.png

[REAL] - [BN]

@lucas_canova Ok, it's more clear. So try with create a new calculated table:

DC_TABLE = DISTINCT(BA_DIARIO[DC])
you have to relate this table with BA_DIARIO and use this new table as column in the matrix.
Then create another new table:
DT_TABLE = DISTINCT(BA_DIARIO[DT_EXEC])
but DON'T relate this new one with BA_DIARIO.
Then calculate measure like:
Sum Switch =
VAR BN6 = CALCULATE(SUM(BA_DIARIO[VLR_LANCAMENTO]) / 1000000, DC_TABLE[BA_DIARIO_DC] = "BN 6 + 6")
VAR MAXDTEXEC = MAX(DT_TABLE[DT_EXEC])
VAR DCMAXEXEC = CALCULATE(SUM(BA_DIARIO[VLR_LANCAMENTO]) /1000000, BA_DIARIO[DT_EXEC] = MAXDTEXEC, ALL(DC_TABLE))
VAR CALC   =
IF(
SELECTEDVALUE (DC_TABLE[BA_DIARIO_DC] ) = "△ REAL X BN", BN6 - DCMAXEXEC, SUM(BA_DIARIO[VLR_LANCAMENTO]) / 1000000)

RETURN CALC
 
and you achieve the output:
BeaBF_0-1722444765540.png

 

 

BBF

@BeaBF Thx so much, idk why, but the solution comes from this buddy:
DT_TABLE = DISTINCT(BA_DIARIO[DT_EXEC])
when i try to use the max date from BA_DIARIO, and dont from this calculate table, i fall to the initial issue.

@lucas_canova to not use an external table for DT_EXEC, you can use:

 

Sum Switch =
VAR BN6 = CALCULATE(SUM(BA_DIARIO[VLR_LANCAMENTO]) / 1000000, DC_TABLE[BA_DIARIO_DC] = "BN 6 + 6")
VAR MAXDTEXEC = CALCULATE(MAX(BA_DIARIO[DT_EXEC]), ALL(BA_DIARIO[DC]), ALL(DC_TABLE[BA_DIARIO_DC]))
VAR DCMAXEXEC = CALCULATE(SUM(BA_DIARIO[VLR_LANCAMENTO]) /1000000, BA_DIARIO[DT_EXEC] = MAXDTEXEC, ALL(DC_TABLE))
VAR CALC   =
IF(
SELECTEDVALUE (DC_TABLE[BA_DIARIO_DC] ) = "△ REAL X BN", BN6 - DCMAXEXEC, SUM(BA_DIARIO[VLR_LANCAMENTO]) / 1000000)

RETURN CALC
 
BBF

@BeaBF , done

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.