Required Dax formula for below table.

Hi Harish85 ,

You can solve this by creating a new calculated table using a DAX formula. This formula will produce a copy of your table that excludes the specific rows based on your condition. Below is the DAX code to accomplish this. Please remember to replace 'YourTable' with the actual name of your data table.

Filtered Sales = 
VAR CustomersWithBothChannels =
    INTERSECT (
        CALCULATETABLE ( VALUES ( 'YourTable'[CUSTOMER_ID] ), 'YourTable'[VTWEG] = 11 ),
        CALCULATETABLE ( VALUES ( 'YourTable'[CUSTOMER_ID] ), 'YourTable'[VTWEG] = 13 )
    )
RETURN
    FILTER (
        'YourTable',
        NOT ( 'YourTable'[CUSTOMER_ID] IN CustomersWithBothChannels && 'YourTable'[VTWEG] = 13 )
    )

To use this formula, navigate to the Data view in Power BI Desktop. In the ribbon, select New Table. You can then paste the DAX formula into the formula bar that appears and press Enter. This action will generate the new filtered table, which will then be available for use in your reports and visuals.

The formula works in two main parts. First, it identifies and creates a temporary list of all CUSTOMER_IDs that have records for both VTWEG = 11 and VTWEG = 13. This list is stored in a variable called CustomersWithBothChannels. Next, the FILTER function iterates through your original table, keeping all rows except for those where the CUSTOMER_ID is in that special list and the VTWEG value is 13. This ensures that for customers with both values, only the row with VTWEG = 11 is kept, while all other records for all other customers remain untouched.

Best regards,

Calculated Column (Flagging Rows to Keep)

ShowRow =
VAR CurrentCustomer = 'YourTable'[CUSTOMER_ID]
VAR HasVTWEG11 =
CALCULATE(
COUNTROWS('YourTable'),
'YourTable'[CUSTOMER_ID] = CurrentCustomer,
'YourTable'[VTWEG] = 11
)
VAR HasVTWEG13 =
CALCULATE(
COUNTROWS('YourTable'),
'YourTable'[CUSTOMER_ID] = CurrentCustomer,
'YourTable'[VTWEG] = 13
)
RETURN
IF(
HasVTWEG11 > 0 && HasVTWEG13 > 0,
IF('YourTable'[VTWEG] = 11, 1, 0),
1
)

Measure (For Visual-Level Filtering)

ShowRowMeasure =
VAR CurrentCustomer = SELECTEDVALUE('YourTable'[CUSTOMER_ID])
VAR CurrentVTWEG = SELECTEDVALUE('YourTable'[VTWEG])
VAR HasVTWEG11 =
CALCULATE(
COUNTROWS('YourTable'),
ALL('YourTable'),
'YourTable'[CUSTOMER_ID] = CurrentCustomer,
'YourTable'[VTWEG] = 11
)
VAR HasVTWEG13 =
CALCULATE(
COUNTROWS('YourTable'),
ALL('YourTable'),
'YourTable'[CUSTOMER_ID] = CurrentCustomer,
'YourTable'[VTWEG] = 13
)
RETURN
IF(
HasVTWEG11 > 0 && HasVTWEG13 > 0,
IF(CurrentVTWEG = 11, 1, 0),
1
)

Use this measure as a visual-level filter:
Filter → ShowRowMeasure = 1

DAX Calculated Column

ShowRow = IF( CALCULATE(COUNTROWS('YourTable'), 'YourTable'[CUSTOMER_ID] = EARLIER('YourTable'[CUSTOMER_ID]), 'YourTable'[VTWEG] = 11) > 0 && CALCULATE(COUNTROWS('YourTable'), 'YourTable'[CUSTOMER_ID] = EARLIER('YourTable'[CUSTOMER_ID]), 'YourTable'[VTWEG] = 13) > 0, IF('YourTable'[VTWEG] = 11, 1, 0), 1 )

Use ShowRow = 1 to filter your table.
Keeps only VTWEG = 11 when both 11 and 13 exist for same CUSTOMER_ID.

Hi Harish85,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to ryan_mayu , Shahid12523 & DataNinja777 for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

Hello Harish85,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

Hi Harish85 ,

Could you mark my post as a solution if it solves your problem. so that other people also can refer it.

Thanks.