topic Re: DAX: 3 measures not working when put in the same table but work when in 3 different tables in DAX Commands and Tips

DAX: 3 measures not working when put in the same table but work when in 3 different tables

Aidadiawndao — Wed, 08 May 2024 14:45:15 GMT

Hello,

I have an issue viewing values in a Power table. I have three measures that work when in 3 different tables, but when put together in the same table, only the one "executed at PIDs level" will show data, no matter if it's in the 1st or last on the table. Below are the 3 measures:

_Calculated_RPEXEC_Executed at PID Level =

VAR _SelectedDate = SELECTEDVALUE(Plant_ID_Period_Key[Date])

VAR _MinDate = CALCULATE (MIN (Plant_ID_Period_Key[Date]))

VAR _ExecCondition = [_Calculated_RPEXEC] // Call the second measure here

VAR _Result = IF (

_ExecCondition = "Executed at PID Level",

CALCULATE (

SUM (Plant_ID_Period_Key[Total Ordered Value]),

DATESBETWEEN (Plant_ID_Period_Key[Date], _MinDate, _SelectedDate)

BLANK() // Return blank if condition is not met

)

RETURN

_Result

_Calculated_RPEXEC_Pending Implementation =

VAR _SelectedDate = SELECTEDVALUE(Plant_ID_Period_Key[Date])

VAR _MinDate = CALCULATE (MIN (Plant_ID_Period_Key[Date]))

VAR _ExecCondition = [_Calculated_RPEXEC] // Call the second measure here

VAR _Result = IF (

_ExecCondition = "Pending Implementation",

CALCULATE (

SUM (Plant_ID_Period_Key[Annual Plan Value]),

DATESBETWEEN (Plant_ID_Period_Key[Date], _MinDate, _SelectedDate)

BLANK() // Return blank if condition is not met

)

RETURN

_Result

_Calculated_RPEXEC_Unplanned at PID Level =

VAR _SelectedDate = SELECTEDVALUE(Plant_ID_Period_Key[Date])

VAR _MinDate = CALCULATE (MIN (Plant_ID_Period_Key[Date]))

VAR _ExecCondition = [_Calculated_RPEXEC] // Call the second measure here

VAR _Result = IF (

_ExecCondition = "Unplanned PID",

CALCULATE (

SUM (Plant_ID_Period_Key[Total Ordered Value]),

DATESBETWEEN (Plant_ID_Period_Key[Date], _MinDate, _SelectedDate)

BLANK() // Return blank if condition is not met

)

RETURN

_Result

Thanks

Re: DAX: 3 measures not working when put in the same table but work when in 3 different tables

Greg_Deckler — Wed, 08 May 2024 14:54:25 GMT

Aidadiawndao Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: DAX: 3 measures not working when put in the same table but work when in 3 different tables

Aidadiawndao — Tue, 14 May 2024 20:42:32 GMT

Pays	ProductID	Period	Annual Plan Value	Total Ordered Value
P1	3000029	2022 Qtr 4	$13,750.00
P1	3000029	2022 Qtr 3	$13,750.00
P1	3000029	2022 Qtr 3	$36,250.00
P1	3000029	2023 Qtr 1	$13,750.00
P1	3000029	2023 Qtr 2	$398,750.00
P1	3000029	2023 Qtr 2	$13,750.00
P1	3000029	2025 Qtr 1	$72,661.00
P1	3000029	2024 Qtr 2	$20,940.00
P1	3000029	2024 Qtr 1	$20,940.00
P1	3000029	2023 Qtr 4	$20,940.00
P1	3000029	2023 Qtr 1		$1,000,000.00
P1	3000029	2023 Qtr 2		$72,451.90

I would like to present this set of data as per the below Visual

Pays

Annual Plan Value

Total Ordered Value

Excecuted based on planned PID (value)

% Excecuted based on planned PID

Unplanned PID (Value)

% Unplanned PID

Pending Implementation (Value)

% Pending Implementation

For that, I have created the below measure as I would like the calculation to be dynamic.

_Sum Annual Plan Value =

VAR _SelectedDate = SELECTEDVALUE(Plant_ID_Period_Key[Date])

VAR _MinDate = CALCULATE (MIN (Plant_ID_Period_Key[Date]))

VAR _Result = CALCULATE ( SUM (Plant_ID_Period_Key[Annual Plan Value]),

DATESBETWEEN (Plant_ID_Period_Key[Date], _MinDate, _SelectedDate) )

RETURN _Result

_Sum Total Ordered Value =

VAR _SelectedDate = SELECTEDVALUE (Plant_ID_Period_Key[Date])

VAR _MinDate = CALCULATE (MIN (Plant_ID_Period_Key[Date]))

VAR _Result = CALCULATE ( SUM (Plant_ID_Period_Key[Total Ordered Value]),

DATESBETWEEN (Plant_ID_Period_Key[Date], _MinDate, _SelectedDate) )

RETURN _Result

_Calculated_RPEXEC =

SWITCH(TRUE(),

NOT(ISBLANK(Plant_ID_Period_Key[_Sum Annual Plan Value])) &&

NOT(ISBLANK([_Sum Total Ordered Value])), "Executed at PID Level",

ISBLANK([_Sum Annual Plan Value]) &&

NOT(ISBLANK([_Sum Total Ordered Value])), "Unplanned PID",

NOT(ISBLANK([_Sum Annual Plan Value])) &&

ISBLANK([_Sum Total Ordered Value]), "Pending Implementation", "NA")

The _Calculated_RPEXEC works perfectly but it's not how we want to present. So I created the below 3 measures that I would to use in the below table

Pays	Annual Plan Value	Total Ordered Value	Excecuted based on planned PID (value)	% Excecuted based on planned PID	Unplanned PID (Value)	% Unplanned PID	Pending Implementation (Value)	% Pending Implementation
			_Calculated_RPEXEC_Executed at PID Level		_Calculated_RPEXEC_Unplanned at PID Level =		_Calculated_RPEXEC_Pending Implementation

_Calculated_RPEXEC_Executed at PID Level =

VAR _SelectedDate = SELECTEDVALUE(Plant_ID_Period_Key[Date])

VAR _MinDate = CALCULATE (MIN (Plant_ID_Period_Key[Date]))

VAR _ExecCondition = [_Calculated_RPEXEC] // Call the second measure here

VAR _Result = IF (

_ExecCondition = "Executed at PID Level",

CALCULATE (

SUM (Plant_ID_Period_Key[Total Ordered Value]),

DATESBETWEEN (Plant_ID_Period_Key[Date], _MinDate, _SelectedDate)

BLANK() // Return blank if condition is not met

)

RETURN

_Result

_Calculated_RPEXEC_Unplanned at PID Level =

VAR _SelectedDate = SELECTEDVALUE(Plant_ID_Period_Key[Date])

VAR _MinDate = CALCULATE (MIN (Plant_ID_Period_Key[Date]))

VAR _ExecCondition = [_Calculated_RPEXEC] // Call the second measure here

VAR _Result = IF (

_ExecCondition = "Unplanned PID",

CALCULATE (

SUM (Plant_ID_Period_Key[Total Ordered Value]),

DATESBETWEEN (Plant_ID_Period_Key[Date], _MinDate, _SelectedDate)

BLANK() // Return blank if condition is not met

)

RETURN

_Result

_Calculated_RPEXEC_Pending Implementation =

VAR _SelectedDate = SELECTEDVALUE(Plant_ID_Period_Key[Date])

VAR _MinDate = CALCULATE (MIN (Plant_ID_Period_Key[Date]))

VAR _ExecCondition = [_Calculated_RPEXEC] // Call the second measure here

VAR _Result = IF (

_ExecCondition = "Pending Implementation",

CALCULATE (

SUM (Plant_ID_Period_Key[Annual Plan Value]),

DATESBETWEEN (Plant_ID_Period_Key[Date], _MinDate, _SelectedDate)

BLANK() // Return blank if condition is not met

)

RETURN

_Result

Problem statement: When these 3 measures are in the same table, only the ones executed at the PID level will work if they have different statuses in the selection.

When the 3 measures are in a separate table and if there is only 1 status in the selection, all 3 measures will work.

Please do let me know if you need additional information

Thank you

Re: DAX: 3 measures not working when put in the same table but work when in 3 different tables

Aidadiawndao — Tue, 14 May 2024 21:06:05 GMT

Pays	ProductID	Period	Annual Plan Value	Total Ordered Value
P1	3000029	2022 Qtr 4	$13,750.00
P1	3000029	2022 Qtr 3	$13,750.00
P1	3000029	2022 Qtr 3	$36,250.00
P1	3000029	2023 Qtr 1	$13,750.00
P1	3000029	2023 Qtr 2	$398,750.00
P1	3000029	2023 Qtr 2	$13,750.00
P1	3000029	2025 Qtr 1	$72,661.00
P1	3000029	2024 Qtr 2	$20,940.00
P1	3000029	2024 Qtr 1	$20,940.00
P1	3000029	2023 Qtr 4	$20,940.00
P1	3000029	2023 Qtr 1		$1,000,000.00
P1	3000029	2023 Qtr 2		$72,451.90

I would like to present this set of data as per the below Visual

Pays

Annual Plan Value

Total Ordered Value

Excecuted based on planned PID (value)

% Excecuted based on planned PID

Unplanned PID (Value)

% Unplanned PID

Pending Implementation (Value)

% Pending Implementation

For that, I have created the below measure as I would like the calculation to be dynamic.

_Sum Annual Plan Value =

VAR _SelectedDate = SELECTEDVALUE(Plant_ID_Period_Key[Date])

VAR _MinDate = CALCULATE (MIN (Plant_ID_Period_Key[Date]))

VAR _Result = CALCULATE ( SUM (Plant_ID_Period_Key[Annual Plan Value]),

DATESBETWEEN (Plant_ID_Period_Key[Date], _MinDate, _SelectedDate) )

RETURN _Result

_Sum Total Ordered Value =

VAR _SelectedDate = SELECTEDVALUE (Plant_ID_Period_Key[Date])

VAR _MinDate = CALCULATE (MIN (Plant_ID_Period_Key[Date]))

VAR _Result = CALCULATE ( SUM (Plant_ID_Period_Key[Total Ordered Value]),

DATESBETWEEN (Plant_ID_Period_Key[Date], _MinDate, _SelectedDate) )

RETURN _Result

_Calculated_RPEXEC =

SWITCH(TRUE(),

NOT(ISBLANK(Plant_ID_Period_Key[_Sum Annual Plan Value])) &&

NOT(ISBLANK([_Sum Total Ordered Value])), "Executed at PID Level",

ISBLANK([_Sum Annual Plan Value]) &&

NOT(ISBLANK([_Sum Total Ordered Value])), "Unplanned PID",

NOT(ISBLANK([_Sum Annual Plan Value])) &&

ISBLANK([_Sum Total Ordered Value]), "Pending Implementation", "NA")

The _Calculated_RPEXEC works perfectly but it's not how we want to present. So I created the below 3 measures that I would to use in the below table

Pays	Annual Plan Value	Total Ordered Value	Excecuted based on planned PID (value)	% Excecuted based on planned PID	Unplanned PID (Value)	% Unplanned PID	Pending Implementation (Value)	% Pending Implementation
			_Calculated_RPEXEC_Executed at PID Level		_Calculated_RPEXEC_Unplanned at PID Level =		_Calculated_RPEXEC_Pending Implementation

_Calculated_RPEXEC_Executed at PID Level =

VAR _SelectedDate = SELECTEDVALUE(Plant_ID_Period_Key[Date])

VAR _MinDate = CALCULATE (MIN (Plant_ID_Period_Key[Date]))

VAR _ExecCondition = [_Calculated_RPEXEC] // Call the second measure here

VAR _Result = IF (

_ExecCondition = "Executed at PID Level",

CALCULATE (

SUM (Plant_ID_Period_Key[Total Ordered Value]),

DATESBETWEEN (Plant_ID_Period_Key[Date], _MinDate, _SelectedDate)

BLANK() // Return blank if condition is not met

)

RETURN

_Result

_Calculated_RPEXEC_Unplanned at PID Level =

VAR _SelectedDate = SELECTEDVALUE(Plant_ID_Period_Key[Date])

VAR _MinDate = CALCULATE (MIN (Plant_ID_Period_Key[Date]))

VAR _ExecCondition = [_Calculated_RPEXEC] // Call the second measure here

VAR _Result = IF (

_ExecCondition = "Unplanned PID",

CALCULATE (

SUM (Plant_ID_Period_Key[Total Ordered Value]),

DATESBETWEEN (Plant_ID_Period_Key[Date], _MinDate, _SelectedDate)

BLANK() // Return blank if condition is not met

)

RETURN

_Result

_Calculated_RPEXEC_Pending Implementation =

VAR _SelectedDate = SELECTEDVALUE(Plant_ID_Period_Key[Date])

VAR _MinDate = CALCULATE (MIN (Plant_ID_Period_Key[Date]))

VAR _ExecCondition = [_Calculated_RPEXEC] // Call the second measure here

VAR _Result = IF (

_ExecCondition = "Pending Implementation",

CALCULATE (

SUM (Plant_ID_Period_Key[Annual Plan Value]),

DATESBETWEEN (Plant_ID_Period_Key[Date], _MinDate, _SelectedDate)

BLANK() // Return blank if condition is not met

)

RETURN

_Result

Problem statement: When these 3 measures are in the same table, only the ones executed at the PID level will work if they have different statuses in the selection.

When the 3 measures are in a separate table and if there is only 1 status in the selection, all 3 measures will work.

Please do let me know if you need additional information

Thank you