topic Re: I can't use a measure in a calculated column in DAX Commands and Tips

I can't use a measure in a calculated column

Bob639 — Tue, 17 Sep 2024 13:03:36 GMT

Hello New to Power BI, I can't use a measure in a calculated column. I created a table with the list of months of the year, then I assigned this list to a segment. In another table, I created a metric to assign

Num1 = SWITCH(SELECTEDVALUE('Sélect_Mois'[Mois]);"Janvier";1;"Février";2;"Mars";3;"Avril";4;"Mai";5;"Juin";6;"Juillet";7;"Août";08;"Septembre";9;"Octobre";10;"Novembre";11;"Décembre";12)

to each month, the number corresponding to the month selected in the segment. I created a column to this table to check if the number of the month is acquired, so far it works. When I use the measure in the function ""it shows me all the dates of the year, the function is Date(2024,[Num1],01) in this case if I select the month of January Num = 1 and date should be = 2024-01-01 but this is not the case for the date it shows me every day of the year for the Num column all the rows are equal to 1. To tell the truth, I use the date function above in another more complex calculated column that doesn't work if I use the measure but works very well if I enter the month directly.

Thank you for your feedback. Kind regards.

Re: I can't use a measure in a calculated column

bhanu_gautam — Tue, 17 Sep 2024 13:09:21 GMT

Bob639 , Try using below in measure

SelectedMonthDate =
VAR SelectedMonthNum = SWITCH(
SELECTEDVALUE('Sélect_Mois'[Mois]),
"Janvier", 1,
"Février", 2,
"Mars", 3,
"Avril", 4,
"Mai", 5,
"Juin", 6,
"Juillet", 7,
"Août", 8,
"Septembre", 9,
"Octobre", 10,
"Novembre", 11,
"Décembre", 12
)
RETURN DATE(2024, SelectedMonthNum, 1)

And that measure in visual

Re: I can't use a measure in a calculated column

Bob639 — Tue, 17 Sep 2024 14:33:10 GMT

This doesn't really work, I have a column: Column = [SelectedMonthDate] doesn't work it's frozen on January when it's July is selected. But if I display the measurement in another column, this works.

Re: I can't use a measure in a calculated column

Anonymous — Thu, 19 Sep 2024 08:24:50 GMT

Hi Bob639

Thank you very much bhanu_gautam for your prompt reply.

The question is whether you are creating a measure or a column.

You make a dynamic selection of months, but in the end you create a calculation column to display the date. This approach violates the way computed columns work.

Because the calculation column is static, when you run the calculation column, it cannot change based on your selection.

At the same time you mentioned that the use of measurement is valid. Because the measurement are dynamic, they can be queried for the month you choose.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: I can't use a measure in a calculated column

Bob639 — Thu, 19 Sep 2024 11:54:49 GMT

Hello,
Thank you for your feedback.
I create a column and use a measure.
I have created a table that filters the data of the database according to the formula below:
"Intervention = VAR NumMois = [Num] RETURN COUNTROWS(FILTER(BD; BD[Dt Début P] < date(2024;8;01) && BD[Etat] = "Programmé" && BD[Bâtiment] = EARLIER('Table 2'[Bâtiment])))".
This formula works.

I want to change the month in "date(2024;8;01)" by the month chosen in the segment.
Do you have a solution?
Kind regards.

Re: I can't use a measure in a calculated column

Anonymous — Fri, 20 Sep 2024 06:48:15 GMT

Hi Bob639

In this case I recommend creating a measure.

The number is dynamically obtained by the month selected in the slicer.

It might look something like this:

Intervention = VAR SelectedMonth = SELECTEDVALUE('Sélect_Mois'[Num1]) RETURN COUNTROWS( FILTER( BD, BD[Dt Début P] < DATE(2024, SelectedMonth, 01) && BD[Etat] = "Programmé" && BD[Bâtiment] = MAX('Table 2'[Bâtiment]) ) )

If you still have problems, you can provide some virtual data.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: I can't use a measure in a calculated column

Bob639 — Fri, 20 Sep 2024 09:28:43 GMT

Hello

The formula does not work.

In the initial Formula 1

Intervention2 = COUNTROWS(FILTER(BD; BD[Dt Début P] < date(2024;8;1) && BD[Etat] = "Programmé" && BD[Bâtiment] = EARLIER('Table 2'[Bâtiment])))

I get the table below

If I replace Formula 1 with

Intervention3 = COUNTROWS(FILTER(BD; BD[Dt Début P] < [SelectedMonthDate] && BD[Etat] = "Programmé" && BD[Bâtiment] = EARLIER('Table 2'[Bâtiment])))

I get an empty table

If I replace Formula 1 with

Intervention3 = COUNTROWS(FILTER(BD; BD[Dt Début P] < [SelectedMonthDate] && BD[Etat] = "Programmé" && BD[Bâtiment] = EARLIER('Table 2'[Bâtiment])))

With the following measurement

SelectedMonthDate =

VAR SelectedMonthNum = SWITCH(

SELECTEDVALUE('Sélect_Mois'[Mois]);

"Janvier";1;

"Février";2;

"Mars";3;

"Avril";4;

"Mai";5;

"Juin";6;

"Juillet";7;

"Août";8;

"Septembre";9;

"Octobre";10;

"Novembre";11;

"Décembre";12

)

RETURN DATE(2024;SelectedMonthNum;1)

I get an empty table

I also created a column in the database (Moisx) to highlight only the month, this column is of the entire type and I integrated it into the formula using the measurement above

Intervention = COUNTROWS(FILTER(BD; BD[Moisx] < [Num_Mois] && BD[Etat] = "Programmé" && BD[Bâtiment] = EARLIER('Table 2'[Bâtiment])))

I get an empty table

I also have to use a measurement that indicates the number of the month selected

Intervention4 = COUNTROWS(FILTER(BD; BD[Dt Début P] < date(2024;[Num_Mois];1) && BD[Etat] = "Programmé" && BD[Bâtiment] = EARLIER('Table 2'[Bâtiment])))

with the measurement

Num_Mois =

VAR Num = SWITCH(

SELECTEDVALUE('Sélect_Mois'[Mois]);

"Janvier";1;

"Février";2;

"Mars";3;

"Avril";4;

"Mai";5;

"Juin";6;

"Juillet";7;

"Août";8;

"Septembre";9;

"Octobre";10;

"Novembre";11;

"Décembre";12

)

RETURN Num

I get an empty table.

To be precise, I checked all the measurements, they do their job.

Kind regards