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
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.
Solved! Go to Solution.
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.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |