Measuring table

Bob639 — Thu, 31 Oct 2024 10:40:55 GMT

Hello,

I have created a table of measures.

Below is the table obtained:

Type =

VAR OriginalData = ADDCOLUMNS(

DISTINCT(BD[Type]);

"Index"; 1

)

VAR NewRow = DATATABLE(

"Type"; STRING;

"Index"; INTEGER;

{

{"Durée"; 2};

{"Total intervention"; 3}

}

)

VAR CombinedData = UNION(OriginalData; NewRow)

RETURN

SELECTCOLUMNS(

CombinedData;

"Type"; [Type];

"Index"; [Index]

)

Each column is presented with the following (the measurements are identical, only what is in bold changes)!:

40 =

var _Bat = "40"

// var _Time = MONTH(NOW()) - 1

var _Month = [Num_Mois]

var _Year = YEAR(NOW())

var _Type = SELECTEDVALUE('Type'[Type])

var _Mois = if ([Num_Mois] + 1 = 13;

1;

[Num_Mois] +1

)

var _An = if ([Num_Mois] + 1 = 13;

YEAR(NOW()) + 1;

YEAR(NOW())

)

var Nb_Interv = COUNTROWS(FILTER(BD;

BD[Bâtiment] = _Bat &&

BD[Type] = _Type &&

BD[Dt Début R] >= date(_Year;_Month;1) &&

BD[Dt Début R] < date(_An;_Mois;1)

)

VAR TotalSomme = CALCULATE(

SUM(BD[Durée R]);

BD[Bâtiment] = _Bat &&

BD[Dt Début R] >= DATE(_Year; _Month; 1) &&

BD[Dt Début R] < DATE(_An; _Mois; 1)

)

RETURN

IF( _Type = "Durée";

TotalSomme;

Nb_Interv

)

The resulting graph:

I had done the same thing in Excel, the table is identical to the one in Power BI but the graph is the one below much more telling:

These work well. With the exception of:

- It doesn't show up in Power Query

- The inverse graph X and Y.

How to make the chart look like the one obtained on Excel.

How to make the sum of each column, lines 1 to 6 and display it on the "Total" row.

Thank you for your feedback.

Kind regards.

Re: Measuring table

Anonymous — Fri, 01 Nov 2024 07:36:55 GMT

Hi Bob639 ,

Based on the description, Power Query is primarily used for data transformation and shaping, and it does not execute DAX measures.

Then, invert the X and Y axis options.

Best Regards,

Wisdom Wu

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

Re: Measuring table

Bob639 — Wed, 06 Nov 2024 09:43:29 GMT

Hello, Thank you for your feedback. Ok for Power Query, for the graph it just inverts the Y scale (below)

It's not what I expect, see initial graph.

Kind regards.

topic Re: Measuring table in DAX Commands and Tips

Measuring table

Type =

VAR OriginalData = ADDCOLUMNS(

DISTINCT(BD[Type]);

"Index"; 1

)

VAR NewRow = DATATABLE(

"Type"; STRING;

"Index"; INTEGER;

{

{"Durée"; 2};

{"Total intervention"; 3}

}

)

VAR CombinedData = UNION(OriginalData; NewRow)

RETURN

SELECTCOLUMNS(

CombinedData;

"Type"; [Type];

"Index"; [Index]

)

40 =

var _Bat = "40"

// var _Time = MONTH(NOW()) - 1

var _Month = [Num_Mois]

var _Year = YEAR(NOW())

var _Type = SELECTEDVALUE('Type'[Type])

var _Mois = if ([Num_Mois] + 1 = 13;

1;

[Num_Mois] +1

)

var _An = if ([Num_Mois] + 1 = 13;

YEAR(NOW()) + 1;

YEAR(NOW())

)

var Nb_Interv = COUNTROWS(FILTER(BD;

BD[Bâtiment] = _Bat &&

BD[Type] = _Type &&

BD[Dt Début R] >= date(_Year;_Month;1) &&

BD[Dt Début R] < date(_An;_Mois;1)

)

)

VAR TotalSomme = CALCULATE(

SUM(BD[Durée R]);

BD[Bâtiment] = _Bat &&

BD[Dt Début R] >= DATE(_Year; _Month; 1) &&

BD[Dt Début R] < DATE(_An; _Mois; 1)

)

RETURN

IF( _Type = "Durée";

TotalSomme;

Nb_Interv

)

Re: Measuring table

Re: Measuring table