Required Dax formula for below table.

[Harish85]

 

HI All,

In the table 74k above columns ,if customer id is same(c240008470) for VTWEG 11 and 13 then we should show only 11 values. Please help with the formula

[v-ssriganesh · Accepted solution]

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

[v-ssriganesh]

Hello @Harish85,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[Aburar_123]

Hi @Harish85 ,

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

Thanks.

[Harish85]

Hi Aburar, thank you very much for  your time. in the below screenshot I marked yellow for customer ID which is repeating,for that VTWEG column has 11,13 and 15 values, but we should show only 11 columns ,same should be repeated for other customer id's also.

[v-ssriganesh]

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.

[v-ssriganesh]

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.

[Shahid12523]

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.

[Shahid12523]

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

[Shahid12523]

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
)

[DataNinja777]

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,

[Harish85]

Sorry 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]

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.

[ryan_mayu]

@Harish85 

you can create a column

Column = if('Table'[CUSTOMER_ID]="C240011427" && 'Table'[VTWEG]=11, 1)

and filter 1 in the table visual

or create a measure

Measure = if(max('Table'[CUSTOMER_ID])="C240011427" && MAX('Table'[VTWEG])=11,1)

and set the measure to 1