Matrix Visual with Specific format and Drill through of values

[Learner27]

Hello, everyone.

I need to create a matrix visual specific formatted. I have over 20 columns, and I want to display the count values in the matrix visual and other calculations.

I'd like to pivot the columns into rows within the matrix visual using the provided sample data.

Firstly, I want to count blank values. If any data has blanks, we need to count them.

Finally, I'd like to identify blank customers on the drill-through page. For example, in the data, there are three blank order numbers, and I want to view those order numbers. I'm adding the required fields for the drill-through page.

I want to see every value in the drill-through page.

Sample Data

samplepbix.pbix

[OwenAuger · Accepted solution]

Hi @Learner27 

My general suggestion would be to construct the Matrix using Calculation Groups for row & column headers.

You could also use some sort of disconnected tables but I found Calculation Groups to be convenient.

Here's what I did in the attached PBIX (see Test Matrix & Test Drillthrough pages).

1. Create measures for each column you have in the row headers of the matrix, aggregated with SELECTEDVALUE.

For example:

SELECTEDVALUE ( Data[name] )

I created 7 such measures.

2. Create a Calculation Group called Calculation Type.

Here is the DAX Script (TE3).

You can extend this to cover other calculation types.

----------------------------------------
-- Calculation Group: 'Calculation Type'
----------------------------------------
CALCULATIONGROUP 'Calculation Type'[Calculation Type]    Description = "Used in columns of matrix"

    CALCULATIONITEM "Blank values count" = 
        -- Counts rows of Data where SELECTEDMEASURE is blank.
        COUNTROWS ( FILTER ( Data, ISBLANK ( SELECTEDMEASURE ( ) ) ) )

    CALCULATIONITEM "Nonblank values count" = 
        -- Counts rows of Data where SELECTEDMEASURE is nonblank.
        COUNTROWS ( FILTER ( Data, NOT ISBLANK ( SELECTEDMEASURE ( ) ) ) )

3. Created a Calculation Group called Measure, with each Calculation Item directly returning one of the measures:

-------------------------------
-- Calculation Group: 'Measure'
-------------------------------
CALCULATIONGROUP 'Measure'[Measure]    Description = "Used in rows of matrix"    Precedence = 1

    CALCULATIONITEM "orderNo" = [orderNo Value]
        Ordinal = 0

    CALCULATIONITEM "name" = [name Value]
        Ordinal = 1

    CALCULATIONITEM "alias name" = [alias name Value]
        Ordinal = 2

    CALCULATIONITEM "address" = [address Value]
        Ordinal = 3

    CALCULATIONITEM "alternate number" = [alternate number Value]
        Ordinal = 3

    CALCULATIONITEM "phoneno" = [phoneno Value]
        Ordinal = 3

4. Create a Dummy measure to use in the matrix (can be anything as it will be overridden).

5. Set the Drillthrough page to drill through based on Dummy measure.

6. On the Drillthrough page's table visual, add a visual level filter Dummy is not blank.

7. Create the source Matrix using:

• Rows = 'Measure'[Measure]
• Columns = 'Calculation Type'[Calculation Type]
• Values = [Dummy]

8. Then it should appear as below, when drilling through from the "3" for example:

[Learner27]

I have attempted to use the SWITCH function;

however, the displayed values are correct. Unfortunately, the drill-through functionality is not working as expected.

Despite having 17 non-blank  order numbers, all the data is being displayed without the filter being applied for drill-through

Here i have uploaded another Pbix file
samplepbix2.pbix

[technolog]

You can use one of the approach:

https://www.selectdistinct.co.uk/2023/02/27/show-values-in-rows-in-power-bi/

https://community.fabric.microsoft.com/t5/Desktop/Need-help-to-insert-space-Blank-Row-in-a-Matrix-Vi...

https://www.popautomation.com/post/power-bi-blank-rows-and-columns

[Learner27]

Hi Technolog, I'm sorry, but the links you posted do not meet my requirements.

I need to see the count values and details on the Drill Through page. I have tried using a switch condition, but it does not seem to apply filters on the Drill Through page. It's possible that the switch function doesn't work on Drill Through, so please suggest an alternative option.

I have created a new table with all the column names in one column and added them to a matrix visual as rows. For the values, I used the switch condition, but it doesn't work on the Drill Through page.

For example, we have 3 blank values in the customer number. When I drill through, I want to see those 3 blank customer numbers and the relevant data


Pleasr help me on this @OwenAuger