Forum Discussion

esilva32's avatar
esilva32
Frequent Visitor
7 years ago
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 ...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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
    _ResTable

     

     

     

  • esilva32's avatar
    esilva32
    7 years ago

    Hello guys

    Thank you for your help. @AIB thank you, worked perfectly, thanks for the help.

    Regards, Portugal.

  • AlB's avatar
    AlB
    7 years ago

    esilva32

     

    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
esilva32's avatar
esilva32
Frequent Visitor
7 years ago

Hello guys

Thank you for your help. @AIB I tried your code and the message was this: it is not possible to convert multiple columns to a scalar value.

Guys, in Power Query I have to build a new table to put this code in it?

AlB's avatar
AlB
Community Champion
7 years ago

esilva32

 

It works fine on my end. Check it out in this file. Maybe you've done something differently.

 

On a different note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

  • esilva32's avatar
    esilva32
    Frequent Visitor
    7 years ago

    Hello guys

    Thank you for your help. @AIB thank you, worked perfectly, thanks for the help.

    Regards, Portugal.

  • esilva32's avatar
    esilva32
    Frequent Visitor
    7 years ago

    Hello @AIB thank you.

    If I want to repeat each line until the end date or for those that the end date is higher than today's date, repeat until today's date, what should I change in the code?

    I sent an example in a pbix file where I want the result of table B from table A.

    Could you help me again? I apologize for the inconvenience

     

    thanks,

     

    http://s000.tinyupload.com/?file_id=66436319019309661221

     

    • AlB's avatar
      AlB
      Community Champion
      7 years ago

      esilva32

       

      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
      • esilva32's avatar
        esilva32
        Frequent Visitor
        7 years ago

        Thanks @ AIB