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

[asan]

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.

[123abc]

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]

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]

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]

@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.

[123abc]

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.