IF empty return result from another table

[Anonymous]

Hi all,

Im hoping you can help me. Apologies in advance for my terrible explanation of my problem(im not a business analyst afterall)

My issue is simple. I have three columns with the number of years, results of our sales and the targets of our sales(see below screenshot).

We have sales for the past years. but since 2020 is not done yet i can't just input the results for 2020. I have another query that pulls through results for 2020 and its from Oracle and it refreshes daily to get the most accurate numbers.

Is there a way using dax/m/query editor that i can make that null value in the results column return a measure from another table that already calculates my results?

So the results for 2020 stand at 30 now but as the year closes more and more sales will come through and i dont want to have to keep updating it manually.

Thanks for your help

[PhilipTreacy]

Hi @Anonymous 

How is the 3 column table generated and therefore how are the values in the result column generated?

You said you have another query that pulls data from Oracle for the 2020 results, and that currently stands at 30.  So on the face of it you should be able to integrate the two queries.

Can you please share the M coe for both of these queries so I can understand how they work?

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

Hi Phillip 

So everything in the screenshot i've shown you is a manually input table using 'enter data'.

The other is a SQL Query that pulls through an extract from our database. Theres no M Code? Sorry im new to Power BI and im not too familiar.

So essentially i just need that 2020 Results Cell to return a value from another table or one of the measures i created. So that it automatically updates at every refresh instead of requiring me. 

Im using the results for a line graph that looks at results by YEAR - Thank you

[PhilipTreacy]

Hi @Anonymous 

When you click on the Enter data icon in PBI and enter data, it creates the M code for you.  If you click on Transform data the Power Query editor opens.  Expand the query pane on the left side of the window and then right-click the query and then click Advanced Editor.  You should now be looking at M code.

The image you've supplied shows the table in the PQ editor, so from there open the Advanced Editor.  Either right click the query as already decribed or click on Advanced Editor on the Home tab.

Regarding the SQL query, how is this executed?  Presumably in Power Query and if so then it also has a PQ query and some M code to get the value for 2020? 

How many queries do you see in the PQ Editor (left hand side of the editor window)?

You can get that 2020 value into the table by referencing the result of the SQL query.

I've prepared a sample to show how this can work, but you will need to adapt it to your situation, as I don't have your code or knowledge of your environment.  If you can provide your PBI file I can try to figure it out.

This codes creates the table you showed in the image above.  There's a separate query called SQLDummy that represents your SQL query.  The output from this is fed into the table using an if statement and a custom column.

You can download this PBIX file which contains both of these queries.

DataTable Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFPSUbI0ABKGBgZKsTpgMXOQmBmqmAVIzAJVzBLINUfVawTiwkRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, TempResult = _t, KPI = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"TempResult", Int64.Type}, {"KPI", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each if [Year] = 2020 then SQLDummy else [TempResult]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"TempResult"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Year", "Result", "KPI"})
in
    #"Reordered Columns"

SQLDummy Query

let
    Source = 30
in
    Source

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

Hi @Anonymous : The explanation seems perfect for the problem statement and its a valid scenario as well

Have you tried using RELATED() Function?

Check this, may be it will help you give some pointers

https://theexcelclub.com/dax-power-pivot-power-bi-ssas-related-and-relatedtable/

Hope this helps.

Cheers,

-Namish B