Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Tricky Concatenate DAX Formula

Hi Experts

 

I have the following dax formula which works fine to a certain limited. i want to change it so if the YTD,MTD and QTD is selected from the date calculation filter and when NO month is selected then the title should read "total revenue summary".

 

i cannot see my error...

 

Title - 1A Product Details = CONCATENATEX( VALUES( 'Date Calculations'[Date Calculation]), 'Date Calculations'[Date Calculation],", ") & " "  
     & IF(HASONEVALUE(DateFilter[Month Short Name]),"",CONCATENATEX (
            VALUES( DateFilter[Month Short Name]),
           DateFilter[Month Short Name],
                ", ")
            &" " & CONCATENATEX( VALUES( DateFilter[Year Nbr]),
                DateFilter[Year Nbr],
            ",")
            & "  Total revenue summary")
  • Hi Anonymous,

     

    The formula seems fine but the result may not be the desired one. Can you share a sample, please? Please mask sensitive parts first.

    Do you really have a column of "YTD, MTD and QTD"?

     

    Best Regards,
    Dale

3 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion

    It is difficult to give you an answer.  You would be well advised to use the Variables Syntax and work your way through the problem one step at a time.  Have a look at the video I did with Avi here.  Watch the video to see how I use VAR to solve complex nested DAX formulas, then apply the process to your formula.

     

    https://www.youtube.com/watch?v=eOJkOWMYSkk 

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks Matt....

       

      i give it a try

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    Hi Anonymous,

     

    The formula seems fine but the result may not be the desired one. Can you share a sample, please? Please mask sensitive parts first.

    Do you really have a column of "YTD, MTD and QTD"?

     

    Best Regards,
    Dale