Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
asan
Frequent Visitor

【NEED HELP DAX】HOW TO APPLY OR FILTER ON MULTI-SELECT SLICERS USING DAX?

Hi,

I'm having a problem writing DAX as a maesure to apply to multi-select slicers on the facttable.

I was planning to use the DAX below until I realized that it can only  used by two slicers.

---------------------------------------------------------------------------------------------------

M_OR条件 =
VAR ForestInfo =
CROSSJOIN(
all('Forest_1'[Forest_1]),
all('Forest_2'[Forest_2]),
all('Forest_3'[Forest_3]),
all('Forest_4'[Forest_4]),
all('Forest_5'[Forest_5]),


VAR SelectedForestInfo =
FILTER(ForestInfo,
IF(OR(
'Forest_1'[Forest_1] IN VALUES ('Forest_1'[Forest_1]),
'Forest_2'[Forest_2] IN VALUES ('Forest_2'[Forest_2]),
'Forest_3'[Forest_3] IN VALUES ('Forest_3'[Forest_3]),
'Forest_4'[Forest_4] IN VALUES (''Forest_4'[Forest_4])

'Forest_5'[Forest_5] IN VALUES 'Forest_5'[Forest_5])))

RETURN
VAR result =
CALCULATE(
SUM('FACT_TABLE_FOREST'[NUMBER]),
SelectedForestInfo
)return result

 

---------------------------------------------------------------------------------------------------

 

Could you help me find another way to solve this?

Thank you.

5 REPLIES 5
123abc
Community Champion
Community Champion

Certainly! It looks like you're trying to filter a fact table based on multiple slicers, and you want to select records where at least one of the selected values from each slicer matches the corresponding field in the fact table. If I understand correctly, you want to filter 'FACT_TABLE_FOREST' based on the selected values in multiple slicers ('Forest_1' to 'Forest_5').

Here's an alternative approach you can consider using DAX. This approach uses the CONTAINSROW function to check if each row in 'FACT_TABLE_FOREST' contains at least one selected value from each slicer:

 

M_OR条件 =
CALCULATE(
SUM('FACT_TABLE_FOREST'[NUMBER]),
FILTER(
'FACT_TABLE_FOREST',
CONTAINSROW(
VALUES('Forest_1'[Forest_1]),
'FACT_TABLE_FOREST'[Forest_1]
)
&&
CONTAINSROW(
VALUES('Forest_2'[Forest_2]),
'FACT_TABLE_FOREST'[Forest_2]
)
&&
CONTAINSROW(
VALUES('Forest_3'[Forest_3]),
'FACT_TABLE_FOREST'[Forest_3]
)
&&
CONTAINSROW(
VALUES('Forest_4'[Forest_4]),
'FACT_TABLE_FOREST'[Forest_4]
)
&&
CONTAINSROW(
VALUES('Forest_5'[Forest_5]),
'FACT_TABLE_FOREST'[Forest_5]
)
)
)

 

In this DAX measure:

  • VALUES('Forest_N'[Field]) returns the selected values from the slicer 'Forest_N'.
  • CONTAINSROW(Values, 'FACT_TABLE_FOREST'[Field]) checks if each row in 'FACT_TABLE_FOREST' contains at least one of the selected values for the corresponding slicer.

This measure should give you the desired result by filtering the 'FACT_TABLE_FOREST' based on the selected values in the slicers 'Forest_1' to 'Forest_5'. Adjust the field names as per your actual data model.

Remember to test this measure with your data to ensure it meets your specific requirements.

 

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

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

asan
Frequent Visitor

Mr. @123abc 
Thank you for returining me.
I really appreciate it.

Certainly! It looks like you're trying to filter a fact table based on multiple slicers, and you want to select records where at least one of the selected values from each slicer matches the corresponding field in the fact table. If I understand correctly, you want to filter 'FACT_TABLE_FOREST' based on the selected values in multiple slicers ('Forest_1' to 'Forest_5').
>Yes, it is exaclty what I want to do.


I tryed this DAX but it seems not working for me.
Each slicers still working as a refine filter.
It supposed to work as OR conditional filter. 
Could you help me find what's the issue here?

Thank you.

123abc
Community Champion
Community Champion

It seems I misunderstood your requirements. If you want to filter your fact table based on an OR condition across different slicers (i.e., if any selected value in any slicer matches the corresponding field in the fact table), you can try the following approach:

 

M_OR条件 =
CALCULATE(
SUM('FACT_TABLE_FOREST'[NUMBER]),
'Forest_1'[Forest_1] IN VALUES('FACT_TABLE_FOREST'[Forest_1]) ||
'Forest_2'[Forest_2] IN VALUES('FACT_TABLE_FOREST'[Forest_2]) ||
'Forest_3'[Forest_3] IN VALUES('FACT_TABLE_FOREST'[Forest_3]) ||
'Forest_4'[Forest_4] IN VALUES('FACT_TABLE_FOREST'[Forest_4]) ||
'Forest_5'[Forest_5] IN VALUES('FACT_TABLE_FOREST'[Forest_5])
)

 

In this revised measure, I'm checking if any selected value in each slicer matches the corresponding field in the fact table ('FACT_TABLE_FOREST'). If at least one of these conditions is true, the row is included in the calculation.

Make sure to replace 'FACT_TABLE_FOREST' with the actual name of your fact table, and adjust column names accordingly. If this still doesn't work as expected, please provide more details about the structure of your data model, and I'll do my best to assist you.

 

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

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

asan
Frequent Visitor

@123abc 
Thank you for returning me so quickly.
Sorry about the inconvinience about my data model.
I will attach the data model and the table capture.

I try the DAX that you send me and there is a error that says 

---The True/False expression does not specify a column
Each True/False expressions used as a tbale filter expression must refer to exactly one column.
---.
 
Could you help me find why is this happening?

----------------------------------------------------
M_OR条件test =
CALCULATE(
SUM('FACT_TABLE_FOREST'[NUMBER]),
'Forest_1'[Forest_1] IN VALUES('FACT_TABLE_FOREST'[Forest_1]) ||
'Forest_2'[Forest_2] IN VALUES('FACT_TABLE_FOREST'[Forest_2]) ||
'Forest_3'[Forest_3] IN VALUES('FACT_TABLE_FOREST'[Forest_3]) 
)
----------------------------------------------------

 

 

 1.DAX that I wrote and the error(It's in Japanese).

It says --The True/False expression does not specify a column
Each True/False expressions used as a tbale filter expression must refer to exactly one column.

---
2.data model structure
3.the visual table and slicers

 

Thank you for your help.

asan_2-1700630002569.png

asan_1-1700629983073.png

asan_0-1700629950282.png

123abc
Community Champion
Community Champion

The error you're encountering is likely due to the fact that the VALUES function returns a table, and you are using it in a context where a single column is expected. To address this, you can modify the measure to use the CONTAINSROW function, which checks if a specific row exists in a table. Here's an adjusted version of your DAX measure:

 

M_OR条件test =
CALCULATE(
SUM('FACT_TABLE_FOREST'[NUMBER]),
CONTAINSROW(VALUES('FACT_TABLE_FOREST'[Forest_1]), 'Forest_1'[Forest_1]) ||
CONTAINSROW(VALUES('FACT_TABLE_FOREST'[Forest_2]), 'Forest_2'[Forest_2]) ||
CONTAINSROW(VALUES('FACT_TABLE_FOREST'[Forest_3]), 'Forest_3'[Forest_3])
)

 

In this measure, CONTAINSROW is used to check if the selected value in each slicer exists in the corresponding column of the 'FACT_TABLE_FOREST'. Ensure that you replace 'Forest_1', 'Forest_2', 'Forest_3' with the actual column names in your data model.

Please try this modification and let me know if it resolves the issue. If you encounter any further problems or if there are additional details about your data model that might affect the solution, feel free to provide more information.

 

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

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors