Forum Discussion
data filtering in matrix visual
Hi Gurpreet-12345 As I can understand that you want the ability to filter matrix with current selection from slicer should be non zero and previous time frame should be zero.
I have tried to implement such scenario using disconnected table, individual measure for each login period, field value and visual level filter measure.
I have a simple data table with only username and login date. Want to count total number of login for each user in a time period.
I have a calendar table and related with date to each other one to many relationship. Check out:Then created countrows measure:
Countrows = IF( ISBLANK(COUNTROWS(LoginTable)), 0, COUNTROWS(LoginTable) )I have 5 time period in calendar table: 7D, 15D, 30D, 60D, and >60D. Check out:
Created 5 individual measure for each time period. Check:
7D = CALCULATE([CountRows], 'Calendar'[TimeFrame] = "7D") 15D = CALCULATE([CountRows], 'Calendar'[TimeFrame] = "15D") 30D = CALCULATE([CountRows], 'Calendar'[TimeFrame] = "30D") 60D = CALCULATE([CountRows], 'Calendar'[TimeFrame] = "60D") >60D = CALCULATE([CountRows], 'Calendar'[TimeFrame] = ">60D")Create a field value without slicer. Check this:
Now created matrix with user (for my case) in rows and newly created TimeFrame fieldvalue in Values section. See my matrix visual:
Now create a disconnected TimeFrame table because we don't want to filter underlying data table. To create a disconnected table with distinct TimeFrame and Index (for sorting in slicer), try use summarize or you can use power query:
Now create a slicer with this TimeFrame column. Now create measure for visual level filter. Try below code:
SelectedMeasure = SWITCH( TRUE(), ISBLANK(SELECTEDVALUE(TimeFrame[TimeFrame])), [7D] + [15D] + [30D] + [60D] + [>60D], SELECTEDVALUE(TimeFrame[TimeFrame]) = "7D", IF([7D] <> 0, [7D], BLANK()), SELECTEDVALUE(TimeFrame[TimeFrame]) = "15D", IF([15D] <> 0 && [7D] = 0, [15D], BLANK()), SELECTEDVALUE(TimeFrame[TimeFrame]) = "30D", IF([30D] <> 0 && [15D] = 0 && [7D] = 0, [30D], BLANK()), SELECTEDVALUE(TimeFrame[TimeFrame]) = "60D", IF([60D] <> 0 && [30D] = 0 && [15D] = 0 && [7D] = 0, [60D], BLANK()), SELECTEDVALUE(TimeFrame[TimeFrame]) = ">60D", IF([>60D] <> 0 && [60D] = 0 && [30D] = 0 && [15D] = 0 && [7D] = 0, [>60D], BLANK()) )Place this measure in filter this visual section in filter pane and set to is not blank. Check out:
You are done. Now you will be able to filter current measure with non zero and all the previous time frame is zero. Check out output:
Output 1 (When 7D selected):Output 2 (When 15D selected):
Output 3 (When 30D Selected):
See, for 30D we have only one rows where current selection is non zero and previous is zeros.
Hope this helps!!If this solved your problem, please accept it as a solution and a kudos!!
Best Regards,
Shahariar Hafiz
To set up your matrix table in Power BI to meet these conditions, you can use DAX measures and filters to control the display based on slicer selections. Here's a step-by-step guide to accomplish this:
Steps:
1. Create the Slicer for Time Frames:
- Set up a slicer with options like 7 days, 30 days, 45 days, 3 months (3M), 6 months (6M), and 1 year (1YR).
- Ensure this slicer is connected to the table that records the login times so that selecting a time frame filters the data accordingly.
2. Define Measures for Each Time Frame:
- Create separate measures to calculate the number of logins for each time frame (e.g., `7d Logins`, `30d Logins`, etc.). These measures will count the logins within the specific timeframe and allow you to apply conditional logic.
- For instance, to get the login count for the last 7 days, you could use a measure like:
DAX
7d Logins =
CALCULATE(
COUNT(Login[LoginID]),
DATESINPERIOD(Date[Date], TODAY(), -7, DAY)
)
- Similarly, create measures for 30 days, 45 days, and so on.
3. Create a Filtered Measure to Exclude Zeros:
- To exclude servers with zero logins in the selected time frame, use a measure that only returns values when the count is greater than zero.
DAX
7d Logins (Filtered) =
IF([7d Logins] > 0, [7d Logins], BLANK())
- Repeat this for each time frame, such as `30d Logins (Filtered)`, `45d Logins (Filtered)`, etc.
4. Create Conditional Display Logic:
- Use DAX to enforce the display conditions for each column based on previous columns. For example, for the 30-day column, you want to display values only if `7d Logins` is zero and `30d Logins` is greater than zero:
DAX
30d Display =
IF([7d Logins] = 0 && [30d Logins] > 0, [30d Logins], BLANK())
- Similarly, create measures for each subsequent timeframe, enforcing that the current timeframe should have logins, and all previous ones should be zero.
5. Add the Measures to Your Matrix Visual:
- Place `Asset Name` or `Server Name` in the Rows section of your matrix.
- Add each of the display measures (e.g., `7d Display`, `30d Display`, `45d Display`, etc.) in the Values section of your matrix visual. This will ensure only the values that meet your conditions are shown.
6. Enable the Correct Filtering Behavior:
- Go to the Visual Level Filters pane and apply filters to each measure to exclude blanks, which will hide the rows with zeros based on the conditions set in your measures.
Please accept this as solution if it helps. Appreciate Kudos.
Hi Thankyou for your reply.I really appreciate it.
Regards
Did shafiz_p's method solve your problem? If yes, could you please mark his reply as a solution? This will help more users facing the same or similar difficulties. Thanks!
Please feel free to let me know if there are still problems.
Best Regards,
Yulia Xu