Unable to use slicers as OR instead of AND, CREATE LEFT JOIN

[k_h_s]

I have build permission on dataset, hence I can not create calculated tables or columns on the model. I need to implement scenarios where 2 slicers should work as or instead of default power bi behaviour (and). Also, I need to join two tables using left join to create the output. My permission can not be upgraded due to RLS. Please help me with a solution. 

I need to replicate 

Basically I want to replicate SELECT
emp.LastName,
emp.FirstName,
emp.JobCode,
emp.JobClass,
emp.CompanyID,
emp.EmployeeID,
det.DetailCode,
det.RateAmount,
det.LimitAmount,
det.VendorID
FROM EmployeeTable AS emp
LEFT OUTER JOIN EmployeeDetailTable AS det
ON emp.EmployeeID = det.EmployeeID
AND emp.CompanyID = det.CompanyID
WHERE
(det.DetailCode = 'XXX' OR det.DetailCode IS NULL)
AND emp.CompanyID = 'YY'
AND emp.IsActive = 'Y'
AND emp.JobCode LIKE '%ZZZ%'
ORDER BY
emp.JobCode ASC,
emp.JobClass ASC;   and SELECT CompanyID, PeriodEndDate, EmployeeID, DetailCode
FROM (
SELECT
CompanyID,
PeriodEndDate,
EmployeeID,
DetailCode,
ROW_NUMBER() OVER (
PARTITION BY EmployeeID, DetailCode
ORDER BY PeriodEndDate DESC
) AS ROW_NUMBER
FROM PayrollDetailTable
WHERE
PeriodEndDate <= 'MM/DD/YYYY'
AND Amount > 0
AND DetailCode BETWEEN 'AAAA' AND 'BBBB'
AND EmployeeID IN (
/* masked list */
)
) t
WHERE t.ROW_NUMBER = 1
ORDER BY EmployeeID ASC, DetailCode ASC; in power bi

[v-priyankata · Accepted solution]

Hi @k_h_s 

Thank you for reaching out to the Microsoft Fabric Forum Community.
@danextian @cengizhanarslan @Kedar_Pande @burakkaragoz Thanks for the inputs.

Attached the power bi file for the given scenario. If need any other filters that should be applied in the visual level filters, please let us know if any additional steps are required from our end.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Thanks

[v-priyankata]

Hi @k_h_s 

Thank you for reaching out to the Microsoft Fabric Forum Community.

I hope the information provided was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @k_h_s 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[k_h_s]

I have build permission on dataset, hence I can not create calculated tables or columns on the model. I need to implement scenarios where 2 slicers should work as or instead of default power bi behaviour (and). Also, I need to join two tables using left join to create the output. My permission can not be upgraded due to RLS. Please help me with a solution. 

[Kedar_Pande]

@cengizhanarslan 

OR Filter =
VAR Slicer1 = SELECTEDVALUE(Table1[Col1])
VAR Slicer2 = SELECTEDVALUE(Table2[Col2])
RETURN
IF(
NOT ISBLANK(Slicer1) || NOT ISBLANK(Slicer2),
1,
BLANK()
)

Left Join Result =
SUMX(
LEFTJOIN(
SUMMARIZE(Table1, Table1[Key]),
SUMMARIZE(Table2, Table2[Key], "Value2", [Measure2])
),
COALESCE([Value2], 0)
)

If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[k_h_s]

Basically I want to replicate SELECT
emp.LastName,
emp.FirstName,
emp.JobCode,
emp.JobClass,
emp.CompanyID,
emp.EmployeeID,
det.DetailCode,
det.RateAmount,
det.LimitAmount,
det.VendorID
FROM EmployeeTable AS emp
LEFT OUTER JOIN EmployeeDetailTable AS det
ON emp.EmployeeID = det.EmployeeID
AND emp.CompanyID = det.CompanyID
WHERE
(det.DetailCode = 'XXX' OR det.DetailCode IS NULL)
AND emp.CompanyID = 'YY'
AND emp.IsActive = 'Y'
AND emp.JobCode LIKE '%ZZZ%'
ORDER BY
emp.JobCode ASC,
emp.JobClass ASC;   and SELECT CompanyID, PeriodEndDate, EmployeeID, DetailCode
FROM (
SELECT
CompanyID,
PeriodEndDate,
EmployeeID,
DetailCode,
ROW_NUMBER() OVER (
PARTITION BY EmployeeID, DetailCode
ORDER BY PeriodEndDate DESC
) AS ROW_NUMBER
FROM PayrollDetailTable
WHERE
PeriodEndDate <= 'MM/DD/YYYY'
AND Amount > 0
AND DetailCode BETWEEN 'AAAA' AND 'BBBB'
AND EmployeeID IN (
/* masked list */
)
) t
WHERE t.ROW_NUMBER = 1
ORDER BY EmployeeID ASC, DetailCode ASC; in power bi

[cengizhanarslan]

Power BI slicers naturally behave like AND when you use two slicers at once. To force OR, you need to filter visuals with a measure that says “keep this row if it matches slicer A or slicer B”. The clean pattern is: take the selected keys from slicer 1 and slicer 2, UNION them, then apply that set to the fact/dimension with TREATAS (or use it as a visual-level filter).

Example idea (adapt column names to your model):

ShowRow_OR =
VAR Keys1 = VALUES ( Dim[Key] )        -- coming from slicer 1
VAR Keys2 = VALUES ( Dim2[Key] )       -- coming from slicer 2 (or another table)
VAR KeysOR = UNION ( Keys1, Keys2 )
RETURN
IF (
    COUNTROWS ( KeysOR ) = 0,
    1,   -- nothing selected -> don't restrict
    IF ( CONTAINS ( KeysOR, Dim[Key], SELECTEDVALUE ( Dim[Key] ) ), 1, 0 )
)

Then you drop ShowRow_OR = 1 into the visual-level filters of the table/matrix/chart you want to behave as OR.

About the LEFT JOIN request: if you can’t create calculated tables, you won’t be able to physically materialize a left join. But you can get the same result in a visual by using fields from the “left” table, and bringing values from the “right” table via a measure (so rows still show even when the right side is blank). A common pattern is LOOKUPVALUE / COALESCE using the left key in the current row context:

RightValue =
VAR k = SELECTEDVALUE ( LeftTable[Key] )
RETURN
COALESCE (
    LOOKUPVALUE ( RightTable[SomeValue], RightTable[Key], k ),
    BLANK()
)

 That behaves like a left join in a table visual: all left rows remain, right-side columns show blanks when there’s no match.

[k_h_s]

Basically I want to replicate SELECT
emp.LastName,
emp.FirstName,
emp.JobCode,
emp.JobClass,
emp.CompanyID,
emp.EmployeeID,
det.DetailCode,
det.RateAmount,
det.LimitAmount,
det.VendorID
FROM EmployeeTable AS emp
LEFT OUTER JOIN EmployeeDetailTable AS det
ON emp.EmployeeID = det.EmployeeID
AND emp.CompanyID = det.CompanyID
WHERE
(det.DetailCode = 'XXX' OR det.DetailCode IS NULL)
AND emp.CompanyID = 'YY'
AND emp.IsActive = 'Y'
AND emp.JobCode LIKE '%ZZZ%'
ORDER BY
emp.JobCode ASC,
emp.JobClass ASC;   and SELECT CompanyID, PeriodEndDate, EmployeeID, DetailCode
FROM (
SELECT
CompanyID,
PeriodEndDate,
EmployeeID,
DetailCode,
ROW_NUMBER() OVER (
PARTITION BY EmployeeID, DetailCode
ORDER BY PeriodEndDate DESC
) AS ROW_NUMBER
FROM PayrollDetailTable
WHERE
PeriodEndDate <= 'MM/DD/YYYY'
AND Amount > 0
AND DetailCode BETWEEN 'AAAA' AND 'BBBB'
AND EmployeeID IN (
/* masked list */
)
) t
WHERE t.ROW_NUMBER = 1
ORDER BY EmployeeID ASC, DetailCode ASC; in power bi

[danextian]

Hi @k_h_s 

SSlicers apply an OR logic only within the same column. OR logic does not work across multiple slicer columns. To achieve this behavior, you would need to create a disconnected table for each dimension you want to filter and use a measure to apply the filter. This cannot be accomplished using build permissions alone—it must be handled in the semantic model. Joining tables must be done in the semantic model as well.

[burakkaragoz]

Hi @k_h_s ,

This is a challenging scenario because Build Permission restricts you from the most common solution (which is creating "Disconnected Tables" for your slicers).

However, you can still solve both problems using DAX Measures, as measures do not require modeling permissions.

Here is the strategy for your constraints:

1. The "OR" Slicer Logic (Visual Filter Method)

Since standard slicers always apply "AND" logic, you must stop them from filtering the visual directly and instead let a Measure decide which rows to show.

Requirement: This only works smoothly if the columns you are slicing on are in separate tables (which seems to be your case since you are asking about joining tables). If the slicers are on the same table, they will auto-hide each other's options.

The Solution:

1. Place your slicers on the canvas using columns from Table A and Table B.

2. Select your main Table visual.

3. Go to Format > Edit Interactions and set the interaction from both slicers to "None" (the circle with a slash) for that Table visual.

   • Now, the slicers will not filter the table at all.

4. Create this Measure and add it to the "Filters on this visual" pane (set to is 1):

OR_Filter_Logic = 
VAR Sel_A = VALUES('Table A'[ColumnA]) -- Get selection from Slicer 1
VAR Sel_B = VALUES('Table B'[ColumnB]) -- Get selection from Slicer 2
VAR Current_Val_A = SELECTEDVALUE('MainTable'[ColumnA])
VAR Current_Val_B = SELECTEDVALUE('MainTable'[ColumnB])

RETURN
    IF(
        -- Condition: Does current row match A OR match B?
        (Current_Val_A IN Sel_A) || (Current_Val_B IN Sel_B),
        1, 
        0
    )

2. The "Virtual" LEFT JOIN

Since you cannot physically merge tables or create relationships, you must simulate a LEFT JOIN using DAX.

The Logic:

• A Left Join keeps all rows from the Left table and brings in matching data from the Right table.

• In Power BI, this means: Use the Left Table's columns for the rows of your visual. Use a Measure to retrieve data from the Right Table.

The Measure (using TREATAS): This is more efficient than LOOKUPVALUE for aggregations.

Left_Join_Value = 
CALCULATE(
    SUM('RightTable'[ValueColumn]), -- The value you want to fetch
    TREATAS(
        VALUES('LeftTable'[JoinKey]), -- The Key from the row context (Left)
        'RightTable'[JoinKey]         -- The Key to match in the Right table
    )
)

How to build the Visual:

1. Drag 'LeftTable'[JoinKey] and 'LeftTable'[Name] into a Table visual.

2. Drag the [Left_Join_Value] measure into the visual.

3. Result: You will see all rows from the Left Table. If a match is found in the Right Table, the value appears. If not, it shows BLANK (just like a SQL Left Join with NULL).

Hope this helps you bypass the modeling restrictions!

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[k_h_s]

Basically I want to replicate SELECT
emp.LastName,
emp.FirstName,
emp.JobCode,
emp.JobClass,
emp.CompanyID,
emp.EmployeeID,
det.DetailCode,
det.RateAmount,
det.LimitAmount,
det.VendorID
FROM EmployeeTable AS emp
LEFT OUTER JOIN EmployeeDetailTable AS det
ON emp.EmployeeID = det.EmployeeID
AND emp.CompanyID = det.CompanyID
WHERE
(det.DetailCode = 'XXX' OR det.DetailCode IS NULL)
AND emp.CompanyID = 'YY'
AND emp.IsActive = 'Y'
AND emp.JobCode LIKE '%ZZZ%'
ORDER BY
emp.JobCode ASC,
emp.JobClass ASC;   and SELECT CompanyID, PeriodEndDate, EmployeeID, DetailCode
FROM (
SELECT
CompanyID,
PeriodEndDate,
EmployeeID,
DetailCode,
ROW_NUMBER() OVER (
PARTITION BY EmployeeID, DetailCode
ORDER BY PeriodEndDate DESC
) AS ROW_NUMBER
FROM PayrollDetailTable
WHERE
PeriodEndDate <= 'MM/DD/YYYY'
AND Amount > 0
AND DetailCode BETWEEN 'AAAA' AND 'BBBB'
AND EmployeeID IN (
/* masked list */
)
) t
WHERE t.ROW_NUMBER = 1
ORDER BY EmployeeID ASC, DetailCode ASC; in power bi

[cengizhanarslan]

Power BI slicers naturally behave like AND when you use two slicers at once. To force OR, you need to filter visuals with a measure that says “keep this row if it matches slicer A or slicer B”. The clean pattern is: take the selected keys from slicer 1 and slicer 2, UNION them, then apply that set to the fact/dimension with TREATAS (or use it as a visual-level filter).

Example idea (adapt column names to your model):

ShowRow_OR =
VAR Keys1 = VALUES ( Dim[Key] )        -- coming from slicer 1
VAR Keys2 = VALUES ( Dim2[Key] )       -- coming from slicer 2 (or another table)
VAR KeysOR = UNION ( Keys1, Keys2 )
RETURN
IF (
    COUNTROWS ( KeysOR ) = 0,
    1,   -- nothing selected -> don't restrict
    IF ( CONTAINS ( KeysOR, Dim[Key], SELECTEDVALUE ( Dim[Key] ) ), 1, 0 )
)

Then you drop ShowRow_OR = 1 into the visual-level filters of the table/matrix/chart you want to behave as OR.

About the LEFT JOIN request: if you can’t create calculated tables, you won’t be able to physically materialize a left join. But you can get the same result in a visual by using fields from the “left” table, and bringing values from the “right” table via a measure (so rows still show even when the right side is blank). A common pattern is LOOKUPVALUE / COALESCE using the left key in the current row context:

RightValue =
VAR k = SELECTEDVALUE ( LeftTable[Key] )
RETURN
COALESCE (
    LOOKUPVALUE ( RightTable[SomeValue], RightTable[Key], k ),
    BLANK()
)

 That behaves like a left join in a table visual: all left rows remain, right-side columns show blanks when there’s no match.