Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Pablo_Aimar
Helper II
Helper II

Need to populate CALCULATED table with dates and other values taken form the lookup table

Hi,

I’m new to this. Please bear with me. I previously worked on data architecting on the Qlik Sense BI platform using their scripting language.  We are now trying to build the same data model in Power BI.  I understand the two tools work differently.

I already have much of the model built.  I work with advertising data.  My PBIX file, can be accessed via WeTransfer:

https://we.tl/t-QyHCoWbYKZ

 

 If you look at my Calculated costs table, you'll see I am taking costs from the following tables: Facebook, LinkedIn, and SA360clicks table.  Each of the rows on these tables have a date and a unique ID.  It is these unique ID's that will allow me to form relationships between all my tables, although I haven't yet formed all the necessary relationships.

 

I also need to take costs from the build sheet table and insert this into the calculated costs table, but the problem is the build sheet table does not have a date on each of its rows.  There are, however, Start Dates and End Dates on each of its rows.  The build sheet table is my lookup table currently, and it groups each advert on a row, and the start date and end date tells us when each advert begins and ends.  If you scroll to the right, you should see a unique ID (placement ID) and a cost-per-day (although not all rows have a cost, that’s fine).

 

My question is: how can I get these build sheet table costs into the Calculated Costs table I made?  I'm thinking this:  write a DAX formula to tell PBI to create (in the calculated costs table) a separate row for each day an advert is active.  Create date, placement ID, and cost-per-day in the calculated costs table. Take this data from the build sheet table.  How do we create separate dates in the calculated costs table when we only have a [start date] and [end date] in the build sheet?

 

 Is this even the best approach? I expect my data sets to grow over time. 

Please help a new user 🙂

1 ACCEPTED SOLUTION

Its not pulling the value of the placement ID, just the text "Placement ID"

CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE ( 'CM', 'CM'[Date], 'CM'[Placement ID] ),
        "Cost-per-day",
            CALCULATE( SUM([Impressions])) / 1000 * 4
    ),
    'CM'[Site] = "Programmatic Ads - Crimtan Agency"
)

View solution in original post

16 REPLIES 16
Pablo_Aimar
Helper II
Helper II

@johnt75  the results looks promising at this stage.  I'll perform further testing of the data, thank-you so much 🙂  will come back with the results

 

LATEST OUTPUT.PNG

Pablo_Aimar
Helper II
Helper II

@johnt75   I've used that in my script, but  we're not seeing the desired results in the outputed table:

 

script.PNG

Its not pulling the value of the placement ID, just the text "Placement ID"

CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE ( 'CM', 'CM'[Date], 'CM'[Placement ID] ),
        "Cost-per-day",
            CALCULATE( SUM([Impressions])) / 1000 * 4
    ),
    'CM'[Site] = "Programmatic Ads - Crimtan Agency"
)
Pablo_Aimar
Helper II
Helper II

@johnt75 

 

the code is accepted by PBI, but it doesn't create the results we were looking for.  Looking at the CM table, filtering to site[CM360] - 'programmatic', we see this placement ID, as an example, 329322853, has over 16,000 impressions.

 

In other words, we'd expect that placement ID (329322853) to now appear in our calculated costs table, and we'd expect a calculated cost too based on our expression (impression/1000)*£4.00

 

However, this ID, 329322853, doesn't appear in our calculated costs table.  It would appear that none of the programmatic placement ID's nor impressions were taken and used from the CM table because if you look at the frontend (screenshot attached), programmatic still has a blank entry in the Spend column:

 

frontend.PNG

I can't figure this out because our code looks fine and makes sense to me

looking at the model data, the column 'CM'[Site (360)] doesn't contain any entries which exactly match "Programmatic", it is "Programmatic Ads - Crimtan Agency"

Pablo_Aimar
Helper II
Helper II

thanks @johnt75 

 

almost there now, but there's one field that PBI won't accept; please see attached screenshot.

I checked this field, and it is in my CM table, and it appears exactly as we've typed it in the code, so I can't see what the issue is.   I even tried adding brackets to our expression, but the error message just won't go away

error.PNG

Its expecting a measue. Replace the Impressions line with

CALCULATE( SUM('CM'[Impressions])) * 1000 / 4

 

Pablo_Aimar
Helper II
Helper II

Hi @johnt75 

 

I am struggling to find where to slot in your code:

 

CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( 'CM', 'CM'[Date] ),
"Placement ID", "Placement ID",
"Cost-per-day",
[Impressions] / 1000 * 4
),
'CM'[Site] = "Programmatic"
)

 

 

..into my existing Calculated Costs-per-day table code:

 

Calculated Costs-per-day table = UNION(SUMMARIZE('Facebook','Facebook'[Date],'Facebook'[Placement ID],'Facebook'[Cost-per-day]),SUMMARIZE('Linkedin','Linkedin'[Date],'Linkedin'[Placement ID],'Linkedin'[Cost-per-day]),SUMMARIZE('SA360CLICKS','SA360CLICKS'[Date],'SA360CLICKS'[Placement ID],'SA360CLICKS'[Cost-per-day]),SELECTCOLUMNS (
GENERATE (
SELECTCOLUMNS (
FILTER ( 'Build Sheet', 'Build Sheet'[Cost-per-day] > 0 ),
"Start Date", 'Build Sheet'[Start Date],
"End Date", 'Build Sheet'[End Date],
"Placement ID", 'Build Sheet'[Placement ID],
"Cost-per-day", 'Build Sheet'[Cost-per-day]
),
CALENDAR ( [Start Date], [End Date] )
),
"Date", [Date],
"Placement ID", [Placement ID],
"Cost-per-day", [Cost-per-day]
)
)
 
I tried adding in your latest block of code at the very end of my existing code, & at various other points, but it keeps returning an error message

I think the below should work

Calculated Costs-per-day table =
UNION (
    SUMMARIZE (
        'Facebook',
        'Facebook'[Date],
        'Facebook'[Placement ID],
        'Facebook'[Cost-per-day]
    ),
    SUMMARIZE (
        'Linkedin',
        'Linkedin'[Date],
        'Linkedin'[Placement ID],
        'Linkedin'[Cost-per-day]
    ),
    SUMMARIZE (
        'SA360CLICKS',
        'SA360CLICKS'[Date],
        'SA360CLICKS'[Placement ID],
        'SA360CLICKS'[Cost-per-day]
    ),
    SELECTCOLUMNS (
        GENERATE (
            SELECTCOLUMNS (
                FILTER ( 'Build Sheet', 'Build Sheet'[Cost-per-day] > 0 ),
                "Start Date", 'Build Sheet'[Start Date],
                "End Date", 'Build Sheet'[End Date],
                "Placement ID", 'Build Sheet'[Placement ID],
                "Cost-per-day", 'Build Sheet'[Cost-per-day]
            ),
            CALENDAR ( [Start Date], [End Date] )
        ),
        "Date", [Date],
        "Placement ID", [Placement ID],
        "Cost-per-day", [Cost-per-day]
    ),
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( 'CM', 'CM'[Date] ),
            "Placement ID", "Placement ID",
            "Cost-per-day",
                [Impressions] / 1000 * 4
        ),
        'CM'[Site] = "Programmatic"
    )
)
Pablo_Aimar
Helper II
Helper II

I will try this and get back to you with my findings, thank-you @johnt75   🙂

Pablo_Aimar
Helper II
Helper II

@johnt75your DAX expressions worked wonderfully, thank-you

I just have one final component to add to my calculated costs table. Please, feel free to take a look at how my data model looks currently: https://we.tl/t-PXzOGPXHVn
You'll see we've integrated costs from the various tables into the separate calculated costs table. There's just one missing component, the programmatic media. Its costs aren't in any of the tables. What I did in my last data model on the Qlik Sense BI platform looked something like this:

CONCATENATE(CostValue)
LOAD
Text("Placement ID") as "Placement ID",
// "Date",
Date(Date#("Date",'YYYY-MM-DD'),'DD/MM/YYYY') as Date,
(Sum(Impressions)/1000) * 4.00 as [Cost-Per-Day]
FROM [lib://Output Files/BBB/CMtable/*_CampaignManager*.qvd] (qvd)
WHERE [Site (DCM)] = 'Programmatic Media'
GROUP BY Date, "Placement ID";

Where media is "Programmatic", we take the metric, [Impressions], from the CM table, divide that by 1,000, then multiple the result by 4.00 (answer in currency format); that's how we get costs [cost-per-day] for this particular media.

How would we integrate this into my PBI calculated costs table?

Lastly, if you look at my separate Date table, how would you form relationships with the other tables? Initially, I thought it would make sense to link the Date table with my lookup table, the Build Sheet table, but the Build Sheet table doesn't have a date on each row. Every other table, however, in my model does have a date on each of its rows.


thank-you

You could add something like the below to the code which builds the calculated costs table

CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE ( 'CM', 'CM'[Date] ),
        "Placement ID", "Placement ID",
        "Cost-per-day",
            [Impressions] / 1000 * 4
    ),
    'CM'[Site] = "Programmatic"
)

As for the date table, I would link that just to the calculated costs table, and have all calculations based on that.

Pablo_Aimar
Helper II
Helper II

thank you @johnt75 I will try this later today 🙂

 

 

Pablo_Aimar
Helper II
Helper II

thank-you @johnt75 ,

Your suggestion gave me what I needed 😊  

However, I see my chosen way of doing things has created a calculated costs table with over 105,000 rows of data.  Put that into context, this is just one month's worth of advertising data.   That's way too much.  Is there anything you'd suggest, looking at my model, that I could do differently?

 

I wish to expand on the DAX code we’ve worked on together.

Is there a way to tell PBI to only take rows from the Build Sheet table that have a value > zero in the cost-per-day column (column AM).  In other words, if the Build Sheet cost-per-day is zero or if there’s no value in that cell at all, please do not take this row for the Calculated Costs table.

This should eliminate many of the redundant rows in my Calculated Costs table.

Any suggestions would be appreciated 😊

 

 

You can get only the rows which have a cost > 0 with

Calculated Costs-per-day table =
UNION (
    SUMMARIZE (
        'Facebook',
        'Facebook'[Date],
        'Facebook'[Placement ID],
        'Facebook'[Cost-per-day]
    ),
    SUMMARIZE (
        'Linkedin',
        'Linkedin'[Date],
        'Linkedin'[Placement ID],
        'Linkedin'[Cost-per-day]
    ),
    SUMMARIZE (
        'SA360CLICKS',
        'SA360CLICKS'[Date],
        'SA360CLICKS'[Placement ID],
        'SA360CLICKS'[Cost-per-day]
    ),
    SELECTCOLUMNS (
        GENERATE (
            SELECTCOLUMNS (
                FILTER ( 'Build Sheet', 'Build Sheet'[Cost-per-day] > 0 ),
                "Start Date", 'Build Sheet'[Start Date],
                "End Date", 'Build Sheet'[End Date],
                "Placement ID", 'Build Sheet'[Placement ID],
                "Cost-per-day", 'Build Sheet'[Cost-per-day]
            ),
            CALENDAR ( [Start Date], [End Date] )
        ),
        "Date", [Date],
        "Placement ID", [Placement ID],
        "Cost-per-day", [Cost-per-day]
    )
)

I wouldn't worry too much about the number of rows, Power BI can comfortably cope with hundreds of millions, even billions, of rows.

johnt75
Super User
Super User

You can use

Calculated Costs-per-day table =
UNION (
    SUMMARIZE (
        'Facebook',
        'Facebook'[Date],
        'Facebook'[Placement ID],
        'Facebook'[Cost-per-day]
    ),
    SUMMARIZE (
        'Linkedin',
        'Linkedin'[Date],
        'Linkedin'[Placement ID],
        'Linkedin'[Cost-per-day]
    ),
    SUMMARIZE (
        'SA360CLICKS',
        'SA360CLICKS'[Date],
        'SA360CLICKS'[Placement ID],
        'SA360CLICKS'[Cost-per-day]
    ),
    SELECTCOLUMNS (
        GENERATE (
            SELECTCOLUMNS (
                'Build Sheet',
                "Start Date", 'Build Sheet'[Start Date],
                "End Date", 'Build Sheet'[End Date],
                "Placement ID", 'Build Sheet'[Placement ID],
                "Cost-per-day", 'Build Sheet'[Cost-per-day]
            ),
            CALENDAR ( [Start Date], [End Date] )
        ),
        "Date", [Date],
        "Placement ID", [Placement ID],
        "Cost-per-day", [Cost-per-day]
    )
)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.