Can somebody help me with the following DAX code that I have included in the attached document please?
I have taken clips of the data model, the two tables that are referenced in the DAX.
I need to calculate the Purchase Duration, but keep getting the following error.
Thanks in advance.DAX Occupancy.docx (714.9 KB)
Hi @chrisgreenslade,
This error appears when you are returning a table instead of a scalar (single) value…
Replace VALUES with an aggregator like: MAX, MIN, SUM etc. if need to capture multiple results: CONCATENATEX.
Purchase Duration =
CALCULATE(
VALUES( 'Tariffs'[Tariff Duration] ),
FILTER(
Tariffs,
'P&D Purchases'[Terminal ID] = 'Tariffs'[Terminal ID ]
&& 'P&D Purchases'[Purchase Start Day Name] = 'Tariffs'[Day Name]
&& 'P&D Purchases'[Purchase Start Date & Time] >= 'Tariffs'[Tariff Start Date]
&& 'P&D Purchases'[Purchase Start Date & Time] <= 'Tariffs'[Tariff End Date]
&& 'P&D Purchases'[Purchase Amount] >= 'Tariffs'[Tariff Amount Start]
&& 'P&D Purchases'[Purchase Amount] <= 'Tariffs'[Tariff Amount End]
&& ( 'P&D Purchases'[Purchase Start Time] >= 'Tariffs'[Tariff Start Time 1] * ( 1 / 24 )
&& 'P&D Purchases'[Purchase Start Time] < 'Tariffs'[Tariff End Time 1] * ( 1 / 24 )
|| 'P&D Purchases'[Purchase Start Time] >= 'Tariffs'[Tariff Start Time 2] * ( 1 / 24 )
&& 'P&D Purchases'[Purchase Start Time] < 'Tariffs'[Tariff End Time 2] * ( 1 / 24 ) )
)
)
I hope this is helpful.
Thanks.
CONCATENATE gives the error
Too few arguments were passed to the CONCATENATE function. The minimum argument count for the function is 2.
As second argument for CONCATENATEX use a delimiter, see the documentation here
Thanks Melissa.
I am so new to Power BI here, that even your document is difficult to read.
Where in my code would I need to use this function and how?
Hi @chrisgreenslade,
No worries. But are you sure you don’t need an aggregation like the sum of durations?
Concatenate returns a text string.
Purchase Duration =
CONCATENATEX(
FILTER(
Tariffs,
'P&D Purchases'[Terminal ID] = 'Tariffs'[Terminal ID ]
&& 'P&D Purchases'[Purchase Start Day Name] = 'Tariffs'[Day Name]
&& 'P&D Purchases'[Purchase Start Date & Time] >= 'Tariffs'[Tariff Start Date]
&& 'P&D Purchases'[Purchase Start Date & Time] <= 'Tariffs'[Tariff End Date]
&& 'P&D Purchases'[Purchase Amount] >= 'Tariffs'[Tariff Amount Start]
&& 'P&D Purchases'[Purchase Amount] <= 'Tariffs'[Tariff Amount End]
&& ( 'P&D Purchases'[Purchase Start Time] >= 'Tariffs'[Tariff Start Time 1] * ( 1 / 24 )
&& 'P&D Purchases'[Purchase Start Time] < 'Tariffs'[Tariff End Time 1] * ( 1 / 24 )
|| 'P&D Purchases'[Purchase Start Time] >= 'Tariffs'[Tariff Start Time 2] * ( 1 / 24 )
&& 'P&D Purchases'[Purchase Start Time] < 'Tariffs'[Tariff End Time 2] * ( 1 / 24 ) )
),
'Tariffs'[Tariff Duration],
", "
)Hi Melissa,
What I need is for it to look up specific purchase start dates with a given terminal ID and then cross reference this to the terminal ID in the Tariff sheet, look at the time of purchase and assign a Purchase Duration.
If that makes sense?
Hi @chrisgreenslade,
Can you supply a sample PBIX?
Here you can also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.
Not really.
The PBIX sources data through our Sharepoint. Moreover, the file is 160MB so I can’t even share through the forum.
Okay there’s another way.
Filter the fact tables in your model down to create just a couple of examples (make sure you have matching records, so a working example), copy those views (entire tables) over to Excel, remove/destort sensitive data and rebuilt your data model on that excel file.
Do you have an email I can send the file to?
Hi @chrisgreenslade,
So the data is in fact irrelevant… I have a mock up sales model that I turned into something that resembles your model.
And I looked up one or more values in the Periods/Tariffs table
Now I’ve stripped it down but it does show how to go about it, see if this works for you
Sample =
CONCATENATEX(
CALCULATETABLE( VALUES(Periods[Period]),
Sales[OrderDate] IN VALUES( Periods[Date] )
), Periods[Period], ", "
)
Here’s my sample. eDNA - test lookup.pbix (400.7 KB)
Hi @chrisgreenslade,
See if any of these methods meet your requirement. Also it seemed like the signs in your initial logic were switched so I changed that and since the results are static, first a Power Query Solution.
AddPurchDuration = Table.AddColumn(#"Changed Type", "Purchase Duration", each
let
pTerminal = [Terminal ID],
pDayName = [Purchase Start Day Name],
pStartDate = Date.From([#"Purchase Start Date & Time"]),
pStartTime = Number.From(Time.From([#"Purchase Start Date & Time"])),
pAmount = Number.From( Text.Remove( [Purchase Amount], "£")),
rTable = Table.SelectRows( BufferedTariff, (BT)=>
BT[Terminal ID] = pTerminal and
BT[Day Name] = pDayName and
BT[Tariff Start Date] <= pStartDate and
BT[Tariff End Date] >= pStartDate and
BT[Tariff Amount Start] <= pAmount and
BT[Tariff Amount End] >= pAmount and
(( Number.From(BT[Tariff Start Time 1]) * ( 1 / 24 ) <= pStartTime and
Number.From(BT[Tariff End Time 1]) * ( 1 / 24 ) > pStartTime ) or
( Number.From(BT[Tariff Start Time 2]) * ( 1 / 24 ) <= pStartTime and
Number.From(BT[Tariff End Time 2]) * ( 1 / 24 ) > pStartTime ))
)
in if Table.RowCount(rTable) >0 then Text.Combine( List.Transform(rTable[Tariff Duration], each Text.From(_)), ", ") else null
.
But this can also be done in DAX, preferred method is as a Measure.
Purchase Duration Measure =
VAR pTerminal = SELECTEDVALUE(Purchase[Terminal ID])
VAR pDayName = SELECTEDVALUE(Purchase[Purchase Start Day Name])
VAR pStartDate = INT( SELECTEDVALUE(Purchase[Purchase Start Date & Time] ))
VAR pStartTime = MOD( SELECTEDVALUE(Purchase[Purchase Start Date & Time]), 1 )
VAR pAmount = SUBSTITUTE( SELECTEDVALUE( Purchase[Purchase Amount]), "£", "" ) *1
RETURN
CONCATENATEX(
FILTER( Tariff,
Tariff[Terminal ID] = pTerminal &&
Tariff[Day Name] = pDayName &&
Tariff[Tariff Start Date] <= pStartDate &&
Tariff[Tariff End Date] >= pStartDate &&
Tariff[Tariff Amount Start] <= pAmount &&
Tariff[Tariff Amount End] >= pAmount &&
( Tariff[Tariff Start Time 1] * (1/24) <= pStartTime &&
Tariff[Tariff End Time 1] * (1/24) > pStartTime ) ||
( Tariff[Tariff Start Time 2] * (1/24) <= pStartTime &&
Tariff[Tariff End Time 2] * (1/24) > pStartTime )
), Tariff[Tariff Duration], ", "
)
.
and finally least preffered method a Calculated Column in DAX
Purchase Duration CC =
VAR pTerminal = Purchase[Terminal ID]
VAR pDayName = Purchase[Purchase Start Day Name]
VAR pStartDate = INT( Purchase[Purchase Start Date & Time] )
VAR pStartTime = MOD( Purchase[Purchase Start Date & Time], 1 )
VAR pAmount = SUBSTITUTE( Purchase[Purchase Amount], "£", "" ) *1
RETURN
CONCATENATEX(
FILTER( Tariff,
Tariff[Terminal ID] = pTerminal &&
Tariff[Day Name] = pDayName &&
Tariff[Tariff Start Date] <= pStartDate &&
Tariff[Tariff End Date] >= pStartDate &&
Tariff[Tariff Amount Start] <= pAmount &&
Tariff[Tariff Amount End] >= pAmount &&
( Tariff[Tariff Start Time 1] * (1/24) <= pStartTime &&
Tariff[Tariff End Time 1] * (1/24) > pStartTime ) ||
( Tariff[Tariff Start Time 2] * (1/24) <= pStartTime &&
Tariff[Tariff End Time 2] * (1/24) > pStartTime )
), Tariff[Tariff Duration], ", "
)
.
Here’s your sample file. eDNA - Lookup with CONTATENATEX.pbix (102.9 KB)
I hope this is helpful.
Hi @chrisgreenslade, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @chrisgreenslade, we’ve noticed that no response has been received from you since the 4th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Thank you.
Hi,
I thought this had worked, but it is bringing up several rows with multiple values in the purchase duration.
I have added a screenshot.Purchase Duration.docx (274.4 KB)
Please provide a sample PBIX that illustrates the issue.
I am unable to because of the file size.