Forum Discussion
Helper IIIRequired Dax formula for below table.
Hello Harish85,
I have reproduced your scenario in Power BI Desktop using the sample data you provided. I implemented a DAX formula to filter the table as per your requirement showing only VTWEG 11 for CUSTOMER_IDs with multiple VTWEG values (e.g: 11, 13, 15), while keeping all rows for CUSTOMER_IDs with a single VTWEG value.
I got the expected output based on your description:
For your reference, I have attached a .pbix file containing the solution, including the sample data and the implemented DAX formula. You can download it, open it in Power BI Desktop, and explore the FilteredSalesData table to see the results.Best regards,
Ganesh Singamshetty
Super UserHi 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,
Helper IIISorry your formula is working there is a small change in the requirement, below is the example ,in below table there are three rows, but we should shown only VTWEG 11 VALUES.
| ZZ_KUNAG | VKORG | VTWEG | ZZ_PARTNER_TYPE | CUSTOMER_ID |
| C240011427 | 2401 | 11 | RE | C240011427 |
| C240011427 | 2401 | 13 | RE | C240011427 |
| C240011427 | 2401 | 15 | RE | C240011427 |
- Aburar_123
Solution Supplier
Hi Harish85 ,
Please try with the below calculated column that helps to choose the minimum VTWEG value.
Required Value Flag =var min_value = CALCULATE(MIN('Table'[VTWEG]),FILTER(ALL('Table'),'Table'[ZZ_KUNAG]=EARLIER('Table'[ZZ_KUNAG])))return if('Table'[VTWEG]=min_value,1)Thanks.
