How to get the unfiltered rows using slicer when two tables are combined

[adityakumar619]

Hi Team,

Please help me out on the below.

I have two tables like below.

Table1: Inv

Table2: Sol

these two tables are connected with many to many of common column i.e SKU

And one slicer with Solution is created,we can easily get the filtered rows based on SKU

My query is how to get the unfiltered rows once we select value i.e say AB solution in slicer? like below

Please find the below dataset and pbix for reference

https://drive.google.com/drive/folders/1IIi86oQRcQ8ZZaKKa2PnHzE8yXwmsTec?usp=sharing 

[v-yanjiang-msft · Accepted solution]

Hi @adityakumar619 ,

According to your description, here’s my solution.

1.Create a column in the table Inv.

Column = RELATED('Sol'[Solution])

2.Create a measure like this.

Check = IF(MAX('Inv'[Column])=SELECTEDVALUE('Sol'[Solution]),1,0)

3.Put the measure in the visual filter.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yanjiang-msft · Accepted solution]

Hi @adityakumar619 ,

I modify the formula like this:

Column = SELECTEDVALUE(Sol[Solution])

It works but you should select the opposite value in the filter, I guess it's the many to many relationship affect the model, I attach my sample bellow, if there is any problem please feel free to let me know.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[adityakumar619 · Accepted solution]

@v-yanjiang-msft One final query,will this apply for many to many but direction is single.I can see above solution applying for Bi direction only. i mean like below?

[v-yanjiang-msft · Accepted solution]

Hi @adityakumar619 ,

According to your description, here’s my solution.

1.Create a column in the table Inv.

Column = RELATED('Sol'[Solution])

2.Create a measure like this.

Check = IF(MAX('Inv'[Column])=SELECTEDVALUE('Sol'[Solution]),1,0)

3.Put the measure in the visual filter.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[adityakumar619 · Accepted solution]

@v-yanjiang-msft One final query,will this apply for many to many but direction is single.I can see above solution applying for Bi direction only. i mean like below?

[v-yanjiang-msft]

Hi @adityakumar619 ,

Because many-to-many relationships often have some problems when quoting, I tried a lot of formulas but there is no suitable one yet. I first thought of the following formula. It doesn’t seem very good, but the problem can be solved first. If there’s a better way I will reply you.

Check is also a measure in table Inv.

Check =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( Sol[Solution] ) = "AB"
        && MAX ( 'Inv'[SKU] ) IN { 121, 122 }, 1,
    SELECTEDVALUE ( Sol[Solution] ) = "CD"
        && MAX ( 'Inv'[SKU] ) = 123, 1,
    SELECTEDVALUE ( Sol[Solution] ) = "EF"
        && MAX ( 'Inv'[SKU] ) = 124, 1,
    SELECTEDVALUE ( Sol[Solution] ) = "GH"
        && MAX ( 'Inv'[SKU] ) = 125, 1,
    SELECTEDVALUE ( Sol[Solution] ) = "IJ"
        && MAX ( 'Inv'[SKU] ) = 126, 1,
    SELECTEDVALUE ( Sol[Solution] ) = "KL"
        && MAX ( 'Inv'[SKU] ) = 127, 1,
    SELECTEDVALUE ( Sol[Solution] ) = "MN"
        && MAX ( 'Inv'[SKU] ) = 128, 1,
    SELECTEDVALUE ( Sol[Solution] ) = "OP"
        && MAX ( 'Inv'[SKU] ) = 129, 1,
    SELECTEDVALUE ( Sol[Solution] ) = "QR"
        && MAX ( 'Inv'[SKU] ) = 129, 1,
    SELECTEDVALUE ( Sol[Solution] ) = "ST"
        && MAX ( 'Inv'[SKU] ) = BLANK (), 0,
    SELECTEDVALUE ( Sol[Solution] ) = "UV"
        && MAX ( 'Inv'[SKU] ) = 130, 1,
    0
)

I also attach my sample bellow for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[adityakumar619 · Accepted solution]

@v-yanjiang-msft thanks for your reply,yes this problem can be solved temporarly but actually iam planning to apply the logic to my business usecase where there were so many Solution names and SKU's.Here in the attached "Check" measure i can see values are hardcored.

Anyhow i really appreciate your kind and patience efforts,i will accept the solution temporarily

And Please let me know if any better solution.

[adityakumar619]

@v-yanjiang-msft Above is perfectly working fine for many-one relationship,But is not working for Many to many relationships.

Iam getting error like this when creating the column with Related function.

Column = RELATED('Sol'[Solution])

"the column 'sol[solution]' either doesn't exist or doesn't have a relationship to any table available in the current context."

How can this applies to many to many relationships

[v-yanjiang-msft · Accepted solution]

Hi @adityakumar619 ,

I modify the formula like this:

Column = SELECTEDVALUE(Sol[Solution])

It works but you should select the opposite value in the filter, I guess it's the many to many relationship affect the model, I attach my sample bellow, if there is any problem please feel free to let me know.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[adityakumar619]

@v-yanjiang-msft Checking on this and will accept your solution very soon,really thanks.

[adityakumar619]

@v-yanjiang-msft Verifying now,will confirm here

[adityakumar619]

Hi Team,Can someone please suggest here,have been trying hard to resolve my query,but no luck

[adityakumar619]

@VahidDM Please find the below link

https://gofile.io/d/YX5faR

[adityakumar619]

@VahidDM were you able to download the dataset and pbix file and pls suggest here

[VahidDM]

Hi @adityakumar619 

It's not possible to download your file, Access required.

Can you share that on https://gofile.io/

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[adityakumar619]

@VahidDM Let me know if you can help here