data filtering in matrix visual

[Gurpreet-12345]

Hello All

Need one solution with my matrix table.I have one matrix visual which shows server names along with the time how many times the system has been logged in ,in the particular time frame. Example:If i click on the slicer of 7 days it should show me all the server names which contain values ,but not the one having zeroes.

Like the first column of 7d should reflect all the servers(asset name) with values,excluding the server name which zeroes.
Similarly,when we click on the slicer button of 30d,it should show all the asset names which have values,but with the requirement its previous column i.e 7d should be zero.First entry should show in 30d column.(see picture for the reference) 

and when we are clicking on 45 days button it should show me all the server names with value,excluding the server name with zeroes and similarly it previous column i.e 7d and 30d should be zero(see M50118612,M5044354 IN BELOW TABLE)

NEED TO FOLLOW THE SAME RULE FOR THE 3M,6M,1YR

Above table is for 3M reference.

Kindly have a look at the requirement and see if someone can help.Thanks in advance.

[shafiz_p · Accepted solution]

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

[Gurpreet-12345]

Hi, Thanks for your response. I really appreciate. This look similar to what I am looking for. Is it possible for you to attach the pbix file?? It would be of great help.

Regards

[shafiz_p]

Here is the PBIX download link:
Download File Here 

Hope this helps!!

If, please mark this as a solution and a kudos!!

[FarhanJeelani]

Hi @Gurpreet-12345 

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.

[Gurpreet-12345]

Hi Thankyou for your reply.I really appreciate it.

Regards

[Anonymous]

Hi @Gurpreet-12345 

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