Forum Discussion

Br1-981's avatar
Br1-981
Icon for Helper I rankHelper I
3 years ago
Solved

Group by and summarize

Hi, I got my raw data like this:

I need to derive, for each campaign the min "Date Start" and the max (Date End). I need also to keep the information about the Budget Owner associated to each campaign, this way:

 

I don't know if the bast way is to create 2 nex measurements or a new table to summarize everything in a group by style

 

Thanks in advance

 

Bruno

 

Need Help
Tips and Tricks
  • sebastiandyrby's avatar
    sebastiandyrby
    3 years ago

    Hi again

    It's true that the Gantt chart doesn't accept the measures as input to the start and/or end dates. However, a workaround is just to add the calculations of start and end dates as calculated columns instead and use these in the Gantt chart. 

    The thing is that - depending on the size of your report - unneccesary tables may affect the performance. And here, I don't think it's necessary.

    So just add calculated columns for you start and end dates like this:

    And use these in the Gantt chart, like this: 

    I then achieve this Gantt chart visual:


    Hope this helps.

    Best regards.

6 Replies

  • sebastiandyrby's avatar
    sebastiandyrby
    Icon for Resolver I rankResolver I
    3 years ago

    Hi Bruno

    You can create a measure in which you filter the table to only look at the current campaign and then take the minimum of the chosen start dates. Like this:

    So creating table visual and inputting the budget owner, the campaign number and then the measure above results in the following:

    And for the end date, a similar measure is just created calculating the maximum instead.

    Hope this helps.

     

    Best regards

    • Br1-981's avatar
      Br1-981
      Icon for Helper I rankHelper I
      3 years ago

      Thanks, the problem is that I need to pass those date to a visual that accept them just if they are from the same table.

      So I created a New table using 

      Summary = SUMMARIZE(Weekly,Weekly[Budget Owner],Weekly[Campaign],)

       To have the unique combination of Budget Owner and Campaigns but I do not know how to add the Columns including calculation of mix and max for Start and End Date

       

      Thanks

      • sebastiandyrby's avatar
        sebastiandyrby
        Icon for Resolver I rankResolver I
        3 years ago

        Hi again

        Not perfectly sure what you're asking for and what you want to achieve. You should be able to use the columns you already have along with the new measures in any visual to achieve something like I showed in the table-screenshot. This could be done without creating a new table with SUMMARIZE.

        But if you can explain more in detail what you want to achieve?

        Best regards.

  • rbouteldja's avatar
    rbouteldja
    New Member
    3 years ago

    Bonjour à tous,

    Pourriez-vous m’aider ?! j’ai des rapports Excel sur Power BI online. J’ai mis des actualisations planifiées.

     

    Or, un message d’erreur s’affiche à chaque actualisation.

     

    ID d’activité: 8cf9aa78-e49d-4f17-b4b4-9d03e0328cc3

    ID de demande: 55e656de-4c04-9708-c84e-f262341a0808

    ID de corrélation: 6f57d558-4bf6-89e6-3f32-25f50cf50f95

    Heure: Mon Feb 20 2023 10:18:23 GMT+0100 (heure normale d’Afrique de l’Ouest)

    Version du service: 13.0.20105.47

    Version du client: 2302.2.12402-train

    URI du cluster: https://wabi-north-europe-h-primary-redirect.analysis.windows.net/

     

    Est-ce que vous avez des explications ? ou des solutions ?

    Je vous remercie par avance

    Redouane,

     

     

  • Br1-981's avatar
    Br1-981
    Icon for Helper I rankHelper I
    3 years ago

    Hi,

    I'm trying to use Custom Visual like Gantt (https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380765?tab=Overview).

    Each campaign has many taks and each of them has a start/end date. I want to show campaigns with start date (so the minimum from its tasks) and end date (the maximum from its tasks), this way:

    The problem is that into the variables it supporting the end date as maximum but not the start date:

     

    If I add the start date I got the details of the tasks I don't want:

     

    So my idea wa to create a new table where I don't have the task details so I create a table summary with:

    Summary = SUMMARIZE(Weekly,Weekly[Budget Owner],Weekly[Campaign])

    and on that table I added some calculated measurements:

    MaxEndDate = 
VAR CampTable=
FILTER(
    ALL(Weekly),
    Weekly[Campaign] =SELECTEDVALUE(Weekly[Campaign])
)
RETURN
MAXX(CampTable,
Weekly[Date End].[Date]
)

     

    MinStartDate = 
VAR CampTable=
FILTER(
    ALL(Weekly),
    Weekly[Campaign] =SELECTEDVALUE(Weekly[Campaign])
)
RETURN
MINX(CampTable,
Weekly[Date Start].[Date]
)
    Spend = 
VAR CampTable=
FILTER(
    ALL(Weekly),
    Weekly[Campaign] =SELECTEDVALUE(Weekly[Campaign])
)
RETURN
SUM(
Weekly[Spend]
)

     

    They are working properly as table:

     

    but the visual does not accept calculated measurement as input so it is like I have to create the table, measurement inclued, in the first step with the SUMMARIZE

    • sebastiandyrby's avatar
      sebastiandyrby
      Icon for Resolver I rankResolver I
      3 years ago

      Hi again

      It's true that the Gantt chart doesn't accept the measures as input to the start and/or end dates. However, a workaround is just to add the calculations of start and end dates as calculated columns instead and use these in the Gantt chart. 

      The thing is that - depending on the size of your report - unneccesary tables may affect the performance. And here, I don't think it's necessary.

      So just add calculated columns for you start and end dates like this:

      And use these in the Gantt chart, like this: 

      I then achieve this Gantt chart visual:


      Hope this helps.

      Best regards.