How to combine lookupvalue with removefilters

[o59393]

Hi all

I have a lookupvalue calculated column that is returning me blank values. I would like to have removed from the lookup function the date field in order to make it work.

Let me explain with the following example:

The calculated column is:

As seen above, the column "MS-SS" is returning me 3 blanks because it can't identify the months on the lookup (Query1) table.

The SKU by line table is: 

 And the Query1 table is:

They have in common the column highlighted in green. 

So all I would like to have is excluded the date-period colum in order to make my calculated column work fine.

How can I incorporate removefilters or any other function?

I attach the excel: https://1drv.ms/u/s!ApgeWwGTKtFdh2FBizn5G-MqMit1?e=2cxuGo

Note: please click on the images for better resolution.

Thanks!

[parry2k · Accepted solution]

@o59393 see attached, created an SKU Master  table in Power Query, and have a relationship with SKU Table and also created a new column using this relationship.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[o59393]

Hi @parry2k 

You solved my problem.

Just one final question and to clear up the doubts I still have in my head.

If I were to use the original M:M both relatinoship with the IF + lookupvalue, there wouldn't be a way to combine the calculated column with a removefilter to ommit the date fields?

For instance instead of:

SKU exists = 

IF (
    LOOKUPVALUE (
        Query1[Merged Plant & Product],
        Query1[Merged Plant & Product], 
        'SKU by line - Official'[Merged Plant & Product]
    ) = 'SKU by line - Official'[Merged Plant & Product],
    "Product in SKU by line exists in catalog",
    "Product in SKU by line doesn't exist in catalog"
)

If I were to try:

SKU exists = 

Var _x = 

IF (
    LOOKUPVALUE (
        Query1[Merged Plant & Product],
        Query1[Merged Plant & Product], 
        'SKU by line - Official'[Merged Plant & Product]
    ) = 'SKU by line - Official'[Merged Plant & Product],
    "Product in SKU by line exists in catalog",
    "Product in SKU by line doesn't exist in catalog"
)

return

CALCULATE(
    _x,
    REMOVEFILTERS(
        Query1[Period[Month 445]]]
    ))

It still wouldn't work.

Would you see any turnaround for the formula or is not possible at all?

Appreciate a lot your help and patience!

[parry2k]

@o59393 I see the issue, you have a relationship between these tables, and relationship filter direction is both. logically there should be a dimension table with unique values which you can easily create in power query or change the relationship from both to query 1 filter SKU table.

[o59393]

Hi @parry2k 

With the query 1 filter SKU table worked but massively raised the wait time of the dashboard. Went from 10 seconds to 2:30 minutes.

Can you advise how to solve with Power Query?

Or would it be possible to keep the M:M both and apply some sort of removefilter date to my initial calculated column? This would be great.

Thanks!

[parry2k]

@o59393 that's why I'm confused, there is some disconnect. seeing your data in pbix will help otherwise what you provided in Excel is working as expected.

[o59393]

Hi @parry2k 

Here is the pbix.

https://1drv.ms/u/s!ApgeWwGTKtFdh2ZeHBm2j0yu3Lll?e=OUWBnN

Notice the relationship must be M:M.

You can filter in the column:

The above one should bring a "Product in SKU by line exists in catalog" because the concatenation does exist.

Could we use some sort of removefilter on date to make it work? 

Thanks.

[parry2k]

@o59393 here is what I did and it worked:

Exists? = 

IF (
    LOOKUPVALUE (
        Query1[Merged Plant & Product],
        Query1[Merged Plant & Product], 
        'SKU by line'[Merged Plant & Product]
    ) = 'SKU by line'[Merged Plant & Product],
    "Product in SKU by line exists in catalog",
    "Product in SKU by line doesn't exist in catalog"
)

 but I'm still not convinced if this is what you are looking for:

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[o59393]

Hi @parry2k 

That's the formula I actually have.

SKU exists = 

IF (
    LOOKUPVALUE (
        Query1[Merged Plant & Product],
        Query1[Merged Plant & Product], 
        'SKU by line - Official'[Merged Plant & Product]
    ) = 'SKU by line - Official'[Merged Plant & Product],
    "Product in SKU by line exists in catalog",
    "Product in SKU by line doesn't exist in catalog"
)

Let me work on a pbix and share with you.

Thanks.

[parry2k]

@o59393 so to clarify, SKU BY Line is a table from which we are checking based on Product and Plan, if records exist in query1, if yes then found else not found. and as per your data, you are expecting every record in SKU BY Line suppose to b found, is this correct understanding?

[o59393]

Correct @parry2k . See my excel attached, column C. If the record exists in Query1 return a yes, else not found. 

Thanks.

[AllisonKennedy]

@o59393  I'm not sure what your final goal is, but I would recommend trying to use relationships in the data model to help you with this if possible. You can create merged columns in Power Query to create a relationship Key column, see this blog for how I've explained how to get a unique key merging two columns, such as what you need to do in this with your products. 

https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_11.html 

https://excelwithallison.blogspot.com/search?q=complicated 

[parry2k]

@o59393 in excel file you already have a column without a date in the merged product column, as I suggested previously why not use that in the lookup:

SKU exists = 

IF (
    LOOKUPVALUE (
        Query1[Merged Plant & Product],
        Query1[Merged Plant & Product], 
        'SKU by line - Official'[Merged Product]
    ) = 'SKU by line - Official'[Merged Product],
    "Product in SKU by line exists in catalog",
    "Product in SKU by line doesn't exist in catalog"
)

[o59393]

Hi @parry2k 

Your formula didn't work, see below:

What I need to have as the solution is the following in column C of the excel attached.

Column D is what it's doing actually (wrong).

Please let me know if you still need the pbix. 

Thanks.

[parry2k]

@o59393 I'm getting more confused as you are replying. Seems like we are on a different page. It will be easier if you share a pbix file with the sample data (remove sensitive information before sharing) and also explain the expected result in the pbix file. It will help to look at things with the same lens.

[o59393]

Ok sure, let me build a pbix and share it with you. This one contains sensitive data I have to remove.

Thanks @parry2k 

[parry2k]

@o59393 seems like I'm missing something here, if you know adding the data in the merged column is causing the issue, why you are using that column in the lookup. I'm totally missing something here.

[o59393]

I am ussing the column Merged Plant & Product to alert the user if the combination product-plant exists or not.

The Query1 is the catalog that includes all the products let's say.

And the SKU by line is manually entered by the user. So I want to alert people that in case the user incorrectly types the product name or plant, you will have a warning "red flag". 

That's why the date is useless for me. All I concern about is the product name and plant concatenation. 

Hopefully I explained myself.

Thanks!

[parry2k]

@o59393 don't know why you are doing a lookup on the column which also has a date added to it, why you are not using the merged product table. It is not clear if query1 also has a date value in the merged column. 

[o59393]

Hi @parry2k 

Long story short, the date is affecting me the result desired below:

The above image issue is similar to the problem stated at the beginning: The concatenation plant and product (Del Valle Peach (Nectar)-KO Flexible Laminate Brick-Pack 200 Milliliter Non Returnable) exists in Query1 but when you add the date it doesn't in some cases (January, February and March).

That is why I need to "remove the existence" of the date column from my formula:

SKU exists = 

IF (
    LOOKUPVALUE (
        Query1[Merged Plant & Product],
        Query1[Merged Plant & Product], 
        'SKU by line - Official'[Merged Plant & Product]
    ) = 'SKU by line - Official'[Merged Plant & Product],
    "Product in SKU by line exists in catalog",
    "Product in SKU by line doesn't exist in catalog"
)

Do you know if it's possible to have the date exclusion in the formula instead of merged columns? that would be great.

Thanks.