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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

DAX true/false based on selected slicer values

Hi! I am hopeing to get some guidance by solving the following challenge. 

 

I have a slicer with values from a seperate slicer table. I would like to return true/false as following. 

 

WM too late = IF(mainTable[Company A] IN VALUES[slicerTable[slicerColumn] && ( mainTable[A too late] = True() ) || IF(mainTable[Company B] IN VALUES[slicerTable[slicerColumn] && ( mainTable[B too late] = True() )

 

This formule always returns true even with no companies selected in the slicer. How can I get the "filtered" true's based on the selected companies. 

 

1 ACCEPTED SOLUTION
technolog
Super User
Super User

The issue you're facing seems to be related to the way DAX evaluates the context and the conditions you've set.

Firstly, let's address the formula you provided:

WM too late = IF(mainTable[Company A] IN VALUES[slicerTable[slicerColumn] && ( mainTable[A too late] = True() ) || IF(mainTable[Company B] IN VALUES[slicerTable[slicerColumn] && ( mainTable[B too late] = True() )
There are some syntax errors in this formula. The correct way to use the IN function and the logical AND (&&) operator is to ensure that each condition is properly enclosed in its own set of parentheses. The corrected formula should look something like this:

WM too late =
IF(
(mainTable[Company A] IN VALUES(slicerTable[slicerColumn])) && (mainTable[A too late] = TRUE()), TRUE(),
IF(
(mainTable[Company B] IN VALUES(slicerTable[slicerColumn])) && (mainTable[B too late] = TRUE()), TRUE(),
FALSE()
)
)
This formula checks if Company A is in the selected slicer values and if A too late is true. If not, it checks the same for Company B. If neither condition is met, it returns FALSE.

Now, regarding the Selected measure you've created:

Selected =
CONCATENATEX(
ALLSELECTED(slicerTable[slicerColumn]),
slicerTable[slicerColumn],
","
)
This measure concatenates all the selected values from the slicer into a single string, separated by commas.

Then, you've created a new column:

New column = CONTAINSSTRING([Selected],"Company A")
This column checks if the Selected measure contains the string "Company A". The reason it always returns TRUE might be because "Company A" is always present in the Selected measure, even if it's not selected in the slicer. This could be due to the way the data is structured or how the slicer is set up.

To troubleshoot this:

Check the slicer setup and ensure that it's correctly linked to the slicerTable[slicerColumn].
Ensure that the Selected measure is correctly reflecting the values chosen in the slicer. If "Company A" is not selected, it shouldn't appear in the Selected measure.
Instead of creating a new column, consider creating a measure. Columns are calculated at the row level and might not always reflect the current filter context, especially when using slicers. Measures, on the other hand, are always calculated in the current filter context.

View solution in original post

2 REPLIES 2
technolog
Super User
Super User

The issue you're facing seems to be related to the way DAX evaluates the context and the conditions you've set.

Firstly, let's address the formula you provided:

WM too late = IF(mainTable[Company A] IN VALUES[slicerTable[slicerColumn] && ( mainTable[A too late] = True() ) || IF(mainTable[Company B] IN VALUES[slicerTable[slicerColumn] && ( mainTable[B too late] = True() )
There are some syntax errors in this formula. The correct way to use the IN function and the logical AND (&&) operator is to ensure that each condition is properly enclosed in its own set of parentheses. The corrected formula should look something like this:

WM too late =
IF(
(mainTable[Company A] IN VALUES(slicerTable[slicerColumn])) && (mainTable[A too late] = TRUE()), TRUE(),
IF(
(mainTable[Company B] IN VALUES(slicerTable[slicerColumn])) && (mainTable[B too late] = TRUE()), TRUE(),
FALSE()
)
)
This formula checks if Company A is in the selected slicer values and if A too late is true. If not, it checks the same for Company B. If neither condition is met, it returns FALSE.

Now, regarding the Selected measure you've created:

Selected =
CONCATENATEX(
ALLSELECTED(slicerTable[slicerColumn]),
slicerTable[slicerColumn],
","
)
This measure concatenates all the selected values from the slicer into a single string, separated by commas.

Then, you've created a new column:

New column = CONTAINSSTRING([Selected],"Company A")
This column checks if the Selected measure contains the string "Company A". The reason it always returns TRUE might be because "Company A" is always present in the Selected measure, even if it's not selected in the slicer. This could be due to the way the data is structured or how the slicer is set up.

To troubleshoot this:

Check the slicer setup and ensure that it's correctly linked to the slicerTable[slicerColumn].
Ensure that the Selected measure is correctly reflecting the values chosen in the slicer. If "Company A" is not selected, it shouldn't appear in the Selected measure.
Instead of creating a new column, consider creating a measure. Columns are calculated at the row level and might not always reflect the current filter context, especially when using slicers. Measures, on the other hand, are always calculated in the current filter context.

Anonymous
Not applicable

Some strange things happen. I made a measure which shows the selected values of my slicer

 

 

Selected = 
CONCATENATEX(
ALLSELECTED(slicerTable[slicerColumn]);
slicerTable[slicerColumn];
","
)

 

 

On a new column:

 

New column = CONTAINSSTRING([Selected];"Company A")

 

 

This new measure always returns true !? A visual of te [selected] measure shows the correct filtering.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.