Issue with Calculated table with User's Selection

[atziovara]

I have an excel table that looks like this:

Financial Year	VAT Number	Sector	Net Sales	EBITDA	Inventories
2019	100	1	10000	9000	800
2019	200	2	12000	9500	900
2020	100	1	15000	10000	1000
2020	200	2	16000	10000	1000
2021	100	1	17000	13000	450
2021	200	2	18000	14000	1200
2022	100	1	19000	15000	800
2022	200	2	20000	16000	800
2023	100	1	21000	17000	1200
2023	200	2	22000	18000	1200

***I have a lot more columns related to the companies' balance sheet items, however I am simplifying it in this example.

I imported the data to PowerBI and inserted a matrix which shows the following: Financial Year, Sum of Net Sales, Sum of EBITDA, Sum of Inventories, etc.

I added 2 slicers: one that filters the years that appear in my matrix ('Sheet1'[Financial Year]), and one that filter the sector ('Sheet1'[Sector]).

I created a calculated table, which contains one column with the VATs that appear in a Common Sample. This Common Sample refers to VATs who appear both in 2022 AND 2023, and have non-zero and non-blank Net Sales in both of those years. If a VAT does not meet this criteria, its value is blank in the calculated table. This table is connected to the original table (Sheet1) via a One-to-many relationship that links 'Net Sales CS 2022-2023'[VALID NET SALESVAT] to 'Sheet1'[VAT Number]. I input this calculated table in a slicer, selected all values apart from the blank ones, and this correctly filters my matrix and shows the Sum of Net Sales, EBITDA, etc, for the filtered common sample.

The type of this table is the following:

Net Sales CS 2022-2023 =

VAR VATs2022 =

    FILTER(

        ALL('Sheet1'),

        'Sheet1'[Financial Year] = 2022 &&

        NOT(ISBLANK('Sheet1'[Net Sales])) &&

        'Sheet1'[Net Sales] <> 0

    )

VAR VATs2023 =

    FILTER(

        ALL('Sheet1'),

        'Sheet1'[Financial Year] = 2023 &&

        NOT(ISBLANK('Sheet1'[Net Sales])) &&

        'Sheet1'[Net Sales] <> 0

    )

RETURN

DISTINCT(

    INTERSECT(

        SELECTCOLUMNS(VATs2022, "VALID NET SALES VAT", 'Sheet1'[VAT Number]),

        SELECTCOLUMNS(VATs2023, "VALID NET SALES VAT", 'Sheet1'[VAT Number])

    )

)

I need help with the following query: Instead of creating a calculated table for the common sample of 2022 and 2023, I would like a calculated table with a dynamic selection of years of Common Sample, based on the users' selection. For example, a user might select the Net Sales Common Sample between 2020, 2021, and 2022. This calculated table (or column, or measure) will be added in a slicer, so that I can filter my matrix and showcase the Sum of Net Sales, EBITDA, Inventories, etc, ONLY FOR the Common Sample.

IMPORTANT: I don't want to create separate measures for each and every column (which means balance sheet item), as there are too many of them in my data.

I created a disconnected table that allows the user to select the years for which he/she wants to compute the common sample. I believe that this should be somehow to the calculated table, but not to the original table. 

YearSelection = DISTINCT('Sheet1'[Financial Year])

However, I didn't have any luck with different tries of creating a calculated table, measure, or column based on my query, SO I WOULD REALLY APPRECIATE YOUR HELP 🙂.

EXTRA QUERY: The user might want to select a different column than the Net Sales one to find the Common Sample of the VATs for selected years.

[v-kathullac]

Hi @atziovara,

Thanks @johnt75  for Addressing the issue.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Chaithanya.

[v-kathullac]

Hi @atziovara,

Thanks @johnt75  for Addressing the issue.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Chaithanya.

[v-kathullac]

Hi @atziovara,

Thanks @johnt75  for Addressing the issue.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Chaithanya.

[johnt75]

You can get the functionality you want by using a calculation group.

First, create a table of all the financial years for use in the slicer,

Years = DISTINCT( Sheet1[Financial Year] )

and add this column to the slicer.

Create measures which sum the columns you are interested in, e.g.

Net Sales Measure = SUM( Sheet1[Net Sales] )

Next create a calculation group and a calculation item like

By Net Sales = 
VAR SelectedYears = VALUES( 'Years'[Financial Year] )
VAR NumSelectedYears = COUNTROWS( SelectedYears )
VAR SummaryTable = ADDCOLUMNS(
    CALCULATETABLE(
        SUMMARIZE( Sheet1, Sheet1[Financial Year], Sheet1[VAT Number] ),
        TREATAS( SelectedYears, Sheet1[Financial Year] ),
        REMOVEFILTERS()
    ),
    "@value", [Net Sales Measure]
)
VAR FilteredTable = GROUPBY(
    FILTER(
        SummaryTable,
        [@value] <> 0
    ),
    Sheet1[VAT Number],
    "@num", SUMX( CURRENTGROUP(), 1)
)
VAR ValidNums = SELECTCOLUMNS(
    FILTER(
        FilteredTable,
        [@num] = NumSelectedYears
    ),
    Sheet1[VAT Number]
)
VAR Result = CALCULATE(
    SELECTEDMEASURE(),
    KEEPFILTERS( ValidNums )
)
RETURN Result

 This code calculates the number of years chosen in the slicer, works out the net sales for each VAT number in each of those years, filters out those years where the net sales is 0 or blank and then counts the number of years for each VAT number, retaining those where the number of valid years matches the number of years selected in the slicer.

If you want to allow the user to choose a measure different from net sales for doing the comparison, create another calculation item and replace the [Net Sales Measure] with the appropriate measure.

[lbendlin]

You cannot create calculated columns/tables from users interacting with the filter context. This only works the other way round.

Calculated columns/tables are calculated once, after the semantic model refresh, and do not interact.