Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hurlingfan11
New Member

Filtering a Matrix Visualization table

Ok so I have the following table extract:

Opp#PAVBU
41660167520PRD
41660168900VRF
417488914200VRF
41777201655PRD

 

And I want the following output for the matrix table below:. 

Opp#VRFPRD
416601689007520

I don't want to display an Opp# where they have a value only for VRF or PRD. The Opp# must have a value for both.

 

This is DAX i used but I still keep getting OP#s in the table that have blank values for either PRD or VRF

FilteredTable2 =

FILTER(
    'tablename',
    NOT(ISBLANK('tablename'[Strategy] = "PRD")) &&
    NOT(ISBLANK('tablename'[Strategy] = "VRF")) &&
    COUNTROWS(
        FILTER(
            'tablename',
            'tablename'[Opp#] = EARLIER('tablename'[Opp#])
        )
    ) == 1
   
)
1 ACCEPTED SOLUTION
PavanLalwani
Advocate I
Advocate I

It seems like you're trying to filter your table to only include rows where an Opp# has both "PRD" and "VRF" values. Based on your description, here's how you can approach this in DAX:

```dax
FilteredTable2 =
FILTER(
SUMMARIZE('tablename', 'tablename'[Opp#], "VRF", CALCULATE(MAX('tablename'[PAV]), 'tablename'[BU] = "VRF"), "PRD", CALCULATE(MAX('tablename'[PAV]), 'tablename'[BU] = "PRD")),
NOT(ISBLANK([VRF])) && NOT(ISBLANK([PRD]))
)
```

### Explanation:

1. **SUMMARIZE Function**: This function creates a summary table that groups by 'Opp#' and calculates the maximum 'PAV' for both "VRF" and "PRD".

2. **CALCULATE Function**: Inside the SUMMARIZE function, CALCULATE is used with MAX to find the maximum 'PAV' where 'BU' (Business Unit) equals "VRF" or "PRD".

3. **NOT(ISBLANK())**: This condition ensures that both "VRF" and "PRD" columns are not blank.

4. **FILTER Function**: Filters the summarized table to include only rows where both "VRF" and "PRD" are not blank.

### Notes:

- Replace `'tablename'` with the actual name of your table.
- `'PAV'`, `'BU'`, `'Opp#'` are assumed column names based on your provided example. Adjust these column names if they differ in your actual dataset.
- This approach ensures that only Opp#s with both "VRF" and "PRD" values are included in your final filtered table.

Make sure to adjust column names and table references according to your actual data model. This DAX expression should give you the desired output where only Opp#s with both "VRF" and "PRD" values are displayed in the matrix table.

View solution in original post

2 REPLIES 2
PavanLalwani
Advocate I
Advocate I

It seems like you're trying to filter your table to only include rows where an Opp# has both "PRD" and "VRF" values. Based on your description, here's how you can approach this in DAX:

```dax
FilteredTable2 =
FILTER(
SUMMARIZE('tablename', 'tablename'[Opp#], "VRF", CALCULATE(MAX('tablename'[PAV]), 'tablename'[BU] = "VRF"), "PRD", CALCULATE(MAX('tablename'[PAV]), 'tablename'[BU] = "PRD")),
NOT(ISBLANK([VRF])) && NOT(ISBLANK([PRD]))
)
```

### Explanation:

1. **SUMMARIZE Function**: This function creates a summary table that groups by 'Opp#' and calculates the maximum 'PAV' for both "VRF" and "PRD".

2. **CALCULATE Function**: Inside the SUMMARIZE function, CALCULATE is used with MAX to find the maximum 'PAV' where 'BU' (Business Unit) equals "VRF" or "PRD".

3. **NOT(ISBLANK())**: This condition ensures that both "VRF" and "PRD" columns are not blank.

4. **FILTER Function**: Filters the summarized table to include only rows where both "VRF" and "PRD" are not blank.

### Notes:

- Replace `'tablename'` with the actual name of your table.
- `'PAV'`, `'BU'`, `'Opp#'` are assumed column names based on your provided example. Adjust these column names if they differ in your actual dataset.
- This approach ensures that only Opp#s with both "VRF" and "PRD" values are included in your final filtered table.

Make sure to adjust column names and table references according to your actual data model. This DAX expression should give you the desired output where only Opp#s with both "VRF" and "PRD" values are displayed in the matrix table.

Thanks Pavan. Solution works. Just needed to change MAX to SUM but this really helped me out. Thanks again for taking the time to review this. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.