Forum Discussion
Solved
DAX Calculation
Hello guys I am performing a calculation with DAX on power BI and would like to know if anyone could help me. My data is in table A and the results I want are in table B. Best regards, Thank you ...
Hi esilva32
It certainly looks like it would be easier to do this in the query editor. If you do need to do it in DAX, try this. Create a new calculated table:
TableB = VAR _BaseTable = ADDCOLUMNS ( GENERATE ( DISTINCT ( TableA[Id] ); GENERATESERIES ( CALCULATE ( DISTINCT ( TableA[StartDate] ) ); CALCULATE ( DISTINCT ( TableA[EndDate] ) ) ) ); "TempVal"; CALCULATE ( DISTINCT ( TableA[Value] ) ) ) VAR _Dates = DISTINCT ( SELECTCOLUMNS ( _BaseTable; "Date"; [Value] ) ) VAR _ResTable = ADDCOLUMNS ( _Dates; "Total Value"; SUMX ( _BaseTable; IF ( [Value] = [Date]; [TempVal] ) ) ) RETURN _ResTableHello guys
Thank you for your help. @AIB thank you, worked perfectly, thanks for the help.
Regards, Portugal.
Minor modification:
TableB = VAR _BaseTable = ADDCOLUMNS ( GENERATE ( DISTINCT ( TableA[Id] ); GENERATESERIES ( CALCULATE ( DISTINCT ( TableA[StartDate] ) ); MIN ( TODAY (); CALCULATE ( DISTINCT ( TableA[EndDate] ) ) ) ) ); "TempVal"; CALCULATE ( DISTINCT ( TableA[Value] ) ) ) VAR _Dates = DISTINCT ( SELECTCOLUMNS ( _BaseTable; "Date"; [Value] ) ) VAR _ResTable = ADDCOLUMNS ( _Dates; "Total Value"; SUMX ( _BaseTable; IF ( [Value] = [Date]; [TempVal] ) ) ) RETURN _ResTable
Minor modification:
TableB =
VAR _BaseTable =
ADDCOLUMNS (
GENERATE (
DISTINCT ( TableA[Id] );
GENERATESERIES (
CALCULATE ( DISTINCT ( TableA[StartDate] ) );
MIN ( TODAY (); CALCULATE ( DISTINCT ( TableA[EndDate] ) ) )
)
);
"TempVal"; CALCULATE ( DISTINCT ( TableA[Value] ) )
)
VAR _Dates =
DISTINCT ( SELECTCOLUMNS ( _BaseTable; "Date"; [Value] ) )
VAR _ResTable =
ADDCOLUMNS (
_Dates;
"Total Value"; SUMX ( _BaseTable; IF ( [Value] = [Date]; [TempVal] ) )
)
RETURN
_ResTableHello people.
I wonder if anyone here has possibilities to help me in PBI.
I need from table A with the fields date_1, time_1, value_1, currency_1, dataInt_1 and datahora_1, create a new table B that is a grouping by date_1, currency_1 and the maximum of datahora_1 and then bring the value_1 that corresponds to each one of these lines
thanks
| data_1 | hora_1 | valor_1 | moeda_1 | dataInt_1 | datahora_1 |
| 01/01/2020 | 93412 | 0,89 | G | 20200101 | 2020010193412 |
| 01/01/2020 | 122402 | 0,78 | G | 20200101 | 20200101122402 |
| 01/01/2020 | 81432 | 111 | C | 20200101 | 2020010181432 |
| 01/01/2020 | 162341 | 109 | C | 20200101 | 20200101162341 |
| 01/01/2020 | 173245 | 0,92 | G | 20200101 | 20200101173245 |
| 01/01/2020 | 102341 | 453 | AO | 20200101 | 20200101102341 |
| 01/01/2020 | 151909 | 390 | AO | 20200101 | 20200101151909 |
| 02/01/2020 | 120405 | 115 | C | 20200102 | 20200102120405 |
| 02/01/2020 | 134232 | 116 | C | 20200102 | 20200102134232 |
| 02/01/2020 | 120923 | 438 | AO | 20200102 | 20200102120923 |
| 02/01/2020 | 84323 | 0,79 | G | 20200102 | 2020010284323 |
| 02/01/2020 | 94500 | 0,84 | G | 20200102 | 2020010294500 |
| 02/01/2020 | 172321 | 110,45 | C | 20200102 | 20200102172321 |