Right, for a returned multiple value check all your filter conditions against the dimension table to identify the issue.
Best of luck.
I have uploaded an excel file with some sample data that highlights the the problem of returning multiple values, even after following your advice and using the DAX that you provided to create a calculated column.
Any help will be gratefully received. Sample File Duration.xlsx (40.9 KB)
Hi @chrisgreenslade,
Some records donât meet all of the criteria and return a blank, I donât have multiple results in the provided sample set. You chose the least favarable option on offer, the calculated column so I included a PQ Custom Column again as well.
Hereâs the sample. eDNA - Lookup with CONTATENATEX v2.pbix (136.9 KB)
Best of luck
Hi @Melissa.
Ideally I wanted to have a measure, but when I tried this using your DAX, it didnât work. Similarly for the PQ option. Which is why I ended up going for the calculated column.
I will see if I can get to the bottom of this.
Thanks
I get the error
Expression.Error: The name âBufferedTariffsâ wasnât recognized. Make sure itâs spelled correctly.
Hi @chrisgreenslade,
That variable, is a manual step I created and references the output of the Tariff query.
Take a look at the first step in your Puchase queryâŚ
Sorry, I really donât understand.
If youâve only copied the logic from the custom column (3) youâre missing a pieceâŚ
You also need to add this step (2) to your query.
Thank you for being so patient with me. So does the BufferedTariff go before the Source step?
Donât mean to confuse you any further but it doesnât really matter where you put it.
Iâve placed it in the Purchase query before the Source step because it calls a different table and when you use the UI to built your queries PQ will automatically reference the previous step for you and you donât need to transform this BufferedTariff only access it for the custom column logicâŚ
I hope this is helpful
OK
So I did apply that step in PQ within my Purchases table. However, I still got the same error as above when I then used a customised column with the code you gave.
So I am not sure what is happening.
Please show the error message
It was this one.
Expression.Error: The name âBufferedTariffsâ wasnât recognized. Make sure itâs spelled correctly.
Post a picture showing the Applied steps pane for that query, thanks
Thanks.
Your step isnât called BufferedTariffs but Buffer(Tariffs) so either change that step name or amend the code and call #âBuffer(Tariffs)â instead ALSO in the last step you are transforming #âBuffer(Tariffs)â youâll need to change that into #âChanged Type1â
So I now have this. My last step was a customised column with your code in.
And I get error in each row of the Duration column created.
Okay mayby I wasnât clear enough
in the last step you are transforming BufferedTariffs youâll need to change that into #âChanged Type1â, so the step before thatâŚ
This refers to the part âIâve placed it in the Purchase query before the Source step because it calls a different table and when you use the UI to built your queries PQ will automatically reference the previous step for you and you donât need to transform this BufferedTariff only access it for the custom column logicâŚâ
When you click off to the side in the white space of the error it might refer to your [Terminal ID] column, if it does go to your Tariffs query and see if there is a non-printable character at the end of that Column name - if so remove that, please.