Measure not responding to right click exclusion on table visual

[tomws2000]

Hello, looking for some help!

I have the below measure on a report page which also has a table visual showing rows with several columns from Rawdata_.

The measure 1.4UNADJUSTEDPeriodA_Measure is stored in the same field of the data model.

Why does this measure not respond to right click > exclude on a table visual that is filtered by a separate TRUE/FALSE measure based on the same time logic as below. I.e. TimeCheck is 1 for the visual table filter.

I need for users to be able to right click and exclude rows from the visual and have the Measure below respond to this, as we have some exceptional rows in data that they may not want. It is not possible to exclude these via tagging and slicing as the nature of the data is not suited to this. There are 3,000+ rows of data so a slicer with the ID of each row is not practicable.


1.4UNADJUSTEDPeriodA_Measure =

--- establish the user time preferences from dashboard disconnected table selections:

VAR SelectedReferenceDate =

    SELECTEDVALUE(Time_Select_PeriodA[EndOfMonth])

VAR SelectedSamplePeriod =

    SELECTEDVALUE(Time_MonthsBackA[N])  

VAR CalculationStartDate =

    EOMONTH(SelectedReferenceDate, -SelectedSamplePeriod)

-- Filter raw data based on selected period

VAR TimeFilteredRows =

    FILTER(

        Rawdata_,

        Rawdata_[Date] <= SelectedReferenceDate &&

        Rawdata_[Date] >= CalculationStartDate

    )

-- Weighted average calculation

VAR TotalValue =

    SUMX(TimeFilteredRows, Rawdata_[Value*Units])

VAR TotalBasis =

    SUMX(TimeFilteredRows, Rawdata_[Units])

VAR WeightedAvg =

    DIVIDE(TotalValue, TotalBasis)

RETURN

    WeightedAvg

[v-sshirivolu · Accepted solution]

Hi @tomws2000 ,
Thanks for reachging out to Microsoft Fabric Community Forum.
To enable dynamic row exclusion, implement a disconnected Exclusions table as follow. Create the Measure:
TotalValue = SUM(RawData[Value])

Add Category and TotalValue to a table visual.

When you right-click and exclude a row, the measure remains unchanged.

Create a Disconnected Exclusions Table:
Exclusions = DISTINCT(RawData[ID])

Keep this table disconnected and add a slicer with Exclusions[ID].

Update the Measure:
TotalValue_ExcludingSelection =
CALCULATE(
    [TotalValue],
    NOT(RawData[ID] IN VALUES(Exclusions[ID]))
)

Add a table visual with Category and TotalValue_ExcludingSelection. Selecting IDs in the slicer will dynamically exclude those rows, effectively simulating the Exclude feature. Apply conditional formatting or use bookmarks and buttons for enhanced interactivity. This method may effectively replicates right-click exclusion functionality.

[v-sshirivolu]

Hi @tomws2000 ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[v-sshirivolu]

Hi @tomws2000 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

Hi @tomws2000 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[rajendraongole1]

Hi @tomws2000  - You need to respect the existing row/visual filters by using ALLSELECTED or simply keeping the existing filter context.

Try the below measure:

1.4UNADJUSTEDPeriodA_Measure =
VAR SelectedReferenceDate =
SELECTEDVALUE(Time_Select_PeriodA[EndOfMonth])

VAR SelectedSamplePeriod =
SELECTEDVALUE(Time_MonthsBackA[N])

VAR CalculationStartDate =
EOMONTH(SelectedReferenceDate, -SelectedSamplePeriod)

-- Keep existing row/visual filters
VAR TimeFilteredRows =
FILTER(
Rawdata_,
Rawdata_[Date] <= SelectedReferenceDate &&
Rawdata_[Date] >= CalculationStartDate
-- No need to remove external filters
)

VAR TotalValue =
SUMX(TimeFilteredRows, Rawdata_[Value*Units])

VAR TotalBasis =
SUMX(TimeFilteredRows, Rawdata_[Units])

VAR WeightedAvg =
DIVIDE(TotalValue, TotalBasis)

RETURN
WeightedAvg

Hope this works. check and confirm.

[tomws2000]

Hi @rajendraongole1  apologies as I cannot see the difference between those dax?

The key seems to be that a right click exclusion in a table showing rows of data
Column Structure for the table visual:
Date - Buyer - Units - Price

I have been asked to make users be able to exclude certain buyers from the table, and have the card visual update in response. This is because certain buyers are key clients and pay a lower rate, but we want to see the average recalculated without them sometimes. The basis for this is discretionary for the user, so simply assigning a classification in the data and excluding via slicer is not a solution, it needs to be possible for users to select and exclude. 

There are two table visuals showing different user selectable periods for comparison, and card visuals with the weighted average price per unit.

a

[tomws2000]

Hi there - thank you so much for your reply. I may be wrong - but I cannot see the difference between the two measures above?