Forum Discussion

hurlingfan11's avatar
hurlingfan11
New Member
2 years ago
Solved

Filtering a Matrix Visualization table

Ok so I have the following table extract: Opp# PAV BU 4166016 7520 PRD 4166016 8900 VRF 4174889 14200 VRF 4177720 1655 PRD   And I want the following output for the mat...
  • PavanLalwani's avatar
    2 years ago

    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.