Calculated Table based on Filter Selection, counting values from another table in the model

[chris_PowerBI]

I have created a Calculated table called Sector Preferences. The purpose of the table is to display calculations from another data table Occupier Data. The primary column of this table contains a list of distinct data points from a single colum in Occupier Data.

The ambition is to drive these calculations (in this example a simple count) based on a user selected filter ( driven for Sectors[Sectors]). So the desired count should be filtered by the value in the primary column in Sector Pereferences and the user input from the Sectors selection.

I have created the first calculated column below but it returns nulls for all rows in my calculate table. If I replace IF(HASONEVALUE(Sectors[Sectors]),Values(Sectors[Sectors]),"Any")  with a quoted string the count works. I therefore believe it is the way I am trying to reference the variable that is causeing the problem.

1 =
VAR SELECTED = IF(HASONEVALUE(Sectors[Sectors]),Values(Sectors[Sectors]),"Any")

Return
CALCULATE(COUNT('Occupier Data'[Out of all of the above Criteria which was of most importance? 1]),filter('Occupier Data','Occupier Data'[Out of all of the above Criteria which was of most importance? 1]=EARLIER('Sector Preferences'[Preferences])),filter('Occupier Data','Occupier Data'[Sector]=SELECTED))

My second challenge is to then enable the calculation of the count when a user does not make a selection ( where the variable Selected becomes "Any". In excel I would use a Wildcard as a criteria but I cannot seem to get to the bottom of how to do this.

Any help would be greatly apprieciated.

[v-lid-msft · Accepted solution]

Hi @chris_PowerBI ,

The measure is dynamic but the calculated columns and tables are computed during the database processing and then stored in the model. So In your scenario, we cannot make the calculated column and tables dynamically.

We can use a table visual with your 'Sector Preferences'[Preferences] and following measure as field to meet your requirement:

Measure =
VAR SELECTED =
    IF ( HASONEVALUE ( Sectors[Sectors] ), VALUES ( Sectors[Sectors] ), "Any" )
RETURN
    CALCULATE (
        COUNT ( 'Occupier Data'[Out of all of the above Criteria which was of most importance? 1] ),
        FILTER (
            'Occupier Data',
            'Occupier Data'[Out of all of the above Criteria which was of most importance? 1]
                = IF (
                    HASONEVALUE ( 'Sector Preferences'[Preferences] ),
                    VALUES ( 'Sector Preferences'[Preferences] )
                )
        ),
        FILTER ( 'Occupier Data', 'Occupier Data'[Sector] = SELECTED )
    )

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.