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

[Anonymous]

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 🙂

[Anonymous]

@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

[Anonymous]

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

[johnt75 · 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"
)

[Anonymous]

@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:

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

[johnt75]

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"

[Anonymous]

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

[johnt75]

Its expecting a measue. Replace the Impressions line with

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

[Anonymous]

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

[johnt75]

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"
    )
)

[Anonymous]

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

[Anonymous]

@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

[johnt75]

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.

[Anonymous]

thank you @johnt75 I will try this later today 🙂

[Anonymous]

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 😊

[johnt75]

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]

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]
    )
)