Forum Discussion
Helper ICombining two slicers
I can't seem to figure out how to properly filter data based on multiple slicers, which are supposed to work together. Here's a dummy example:
In the exampe, if I check "Show only latest", the table shows correct data, but "Values present" is gone, presumably because on latest date (January 3th) there are no rows with all values. Same thing the other way around, if I select "Values present", then "Show only latest values" disappears:
How do I make the slicers work so I can filter the table based on both selection: Selecting "Values present" and "Show only latest" would return latest rows where all values are present. Also all options on both slicers should be visible at all times.
The code for the "All values present" calculated column is:
All values present = IF(
ISBLANK(testData[Value1])
|| ISBLANK(testData[Value2])
|| ISBLANK(testData[Value3]),
"Values missing",
"Values present"
)
Show only latest values:
Show only latest values =
VAR latestTime = CALCULATE(
MAX('testData'[Date]),
ALLEXCEPT(testData, testData[ItemId])
)
RETURN IF(
CALCULATE(MAX(testData[Date]) = latestTime),
"Yes"
)
After several hours of trying various things, I solved the issue by just adding the "All values present" to the ALLEXCEPT in the "Show only latest values" column:
Show only latest values = VAR latestTime = CALCULATE( MAX('testData'[Date]), ALLEXCEPT(testData, testData[ItemId], testData[All values present]) // Added extra argument ) RETURN IF( CALCULATE(MAX(testData[Date]) = latestTime), "Yes" )
5 Replies
- BA_Pete
Super User
Hi JKoivu ,
You might be able to do this by changing the visual interactions between the slicers.
Select one of your slicers then, on the Format tab that appears, toggle 'Edit interactions' on. You will then see icons appearing above all the other visuals where you can choose whether the selected slicer filters it or not.
Change the interaction with the other slicers to 'None', then do the same for each of the other slicers by selecting them on the report page and changing the interactions.
Hope this makes sense.
Pete
Tahreem24JKoivu ,
If you don't want your two slicer to interact with each other so try to edit interaction feature of power bi.
Just click on "All Value present" slicer then go to Format option --. Edit Interaction --> click on "latest value slicer" then click on None (blocked) icon. Likewise, do the same for vice versa.
- JKoivu
Tahreem24 BA_Pete Thanks for your replies. I now prevented the interaction between the slicers, and the options are visible at all times, which is what I want. However, when I select "Values present" and "Show latest", the table is empty, as there are no rows where date is January 3th and all values are present. With the aforementioned selections I should see rows where the values were all present previously
- JKoivu
I thought that I could summarize by "All values present" and then selecting the max date, but this also didn't work.
Show only latest values = VAR groupedByDataPresent = SUMMARIZE( testData, testData[All values present], "Date", testData[Date] ) VAR latestTime = MAXX(groupedByDataPresent, [Date]) RETURN IF( CALCULATE(MAX(testData[Date]) = latestTime), "Yes" )Just to clarify, with the visible selections only the highlighted rows should be visible:
- JKoivu
After several hours of trying various things, I solved the issue by just adding the "All values present" to the ALLEXCEPT in the "Show only latest values" column:
Show only latest values = VAR latestTime = CALCULATE( MAX('testData'[Date]), ALLEXCEPT(testData, testData[ItemId], testData[All values present]) // Added extra argument ) RETURN IF( CALCULATE(MAX(testData[Date]) = latestTime), "Yes" )
