Two views both with ref field but needs different sets of values, possible?

[Mahesh]

I have master data (let’s say Items) of different type in its table (green box below).

Two views use a “ref” column on the Item table, each needing to show item of certain type only. So view1 dropdown needs to show Type1 items while view2 need to show Type2 items.

Using a slice as datasource for Ref column to restrict to type1 items will help one view but not the other.

Any ideas? Thanks!

As they say picture worth a thousand words. Hopefully this will describe the problem quickly.

[LeventK]

@Mahesh
You can use a Valid_if in the REF column:

SELECT(Tablename[KeyColumn],[Type]="Type1")

OR

SELECT(Tablename[KeyColumn],[Type]=[_THISROW].[Type])

[Mahesh]

Thanks a lot @LeventK, that’s a very good idea.

SELECT(Tablename[KeyColumn],[Type]=[_THISROW].[Type])

would be closer to what I need. However the right side needs be decided by the view.

So instead of ([_THISROW].[Type]) I guess I can embed another expression in its place. Something like
IF ( CONTEXT(“View”) = “View1”, “TypeA”, “TypeB”) into the SELECT to get the behavior?

Thoughts?

[LeventK]

@Mahesh
Haven’t ever tried like that but if your idea is splitting the dropdown content as per Form View than I believe you can do it like this assuming that you will be having REF type FORM Views for each option:

IFS(
	AND(
		CONTEXT("ViewType")="form",
		CONTEXT("View")="FormA"
	),SELECT(TableName[KeyColumn],[Type]="Type1"),
	AND(
		CONTEXT("ViewType")="form",
		CONTEXT("View")="FormB"
	),SELECT(TableName[KeyColumn],[Type]="Type2"),
	TRUE,SELECT(TableName[KeyColumn],[Type]=[_THISROW].[Type])
)

[Mahesh]

@LeventK, will try and get back. thx

[Mahesh]

@LeventK, thanks to your idea I was able to solve the issue.
The solution had to be implemented little differently.
For some reason using valid_if did not work but applied that idea using CONTEXT to the filter on the slice used for the REF field. That worked perfectly.

May be it my incorrect implementation of valid_if. Just to try I put in a quick-and-dirty boolean condition in valid_if. I think I said something like "TableName[Type] == “TypeA” but it still returned all records.

[LeventK]

@Mahesh
TableName[Type] == “TypeA is not a valid expression. Did the expression builder verified this??

[Mahesh]

I may be remembering filter clause incorrectly but it was verified by the expression builder because I ran the app after I put in the expression. Let me try it again and get back to you with the exact filter clause.

Assuming that will work, I am curious, what are the pro/cons of the two approaches: Using filter clause on the slice versus using filter clause on the valid_if of the Ref column?

Thanks!