CODEWITHSUNDEEP
powerbidax
Black Rock Shooter
Black Rock Shooter

Reputation: 26

Replicating a value on each row

I have the following three tables:

Clients Table

Clients Table

Calendar Table

Calendar Table

Budget Table

Budget Table

ClientUK is a calculated column, it's just a simple concatenation of the ClientID and Company columns.

Do keep in mind that while a client can be in more than one company, it will always have the same BU regardless.

The budget value is always on the last day of the month, what I need is for that budget to be replicated on each day, so that I can later work with it to make a weighted average.

The problem I have is that I can either choose with only one company at the time:

BDG = 
VAR SearchDate = EOMONTH(SELECTEDVALUE(Calendar[Date]); 0)
VAR SearchBU = SELECTEDVALUE(Clients[BU])
VAR SearchCompany = SELECTEDVALUE(Clients[Company])

RETURN
    CALCULATE (
        SUM ( Budget[ValueBDG] );
        FILTER (
            ALLNOBLANKROW ( Calendar[Date] );
            Calendar[Date] = SearchDate
        );
        FILTER (
            ALLNOBLANKROW ( Clients[BU] );
            Clients[BU] = SearchBU
        );
        FILTER (
            ALLNOBLANKROW ( Clients[Company] );
            Clients[Company] = SearchCompany
        );
        ALL ( Budget )
    )

Or have a sum of all three that disregards any company filter:

BDG = 
VAR _SearchDate = EOMONTH(SELECTEDVALUE(Calendar[Date]); 0)
VAR _SearchBU = SELECTEDVALUE(Clients[BU])
--VAR _SearchCompany = SELECTEDVALUE(Clients[Company])

--VAR _FilterCompany = FILTER ( ALLNOBLANKROW ( Clients[Company] ); Clients[Company] = _SearchCompany )

RETURN
    CALCULATE (
        SUM ( Budget[ValueBDG] );
        FILTER (
            ALLNOBLANKROW ( Calendar[Date] );
            Calendar[Date] = _SearchDate
        );
        FILTER (
            ALLNOBLANKROW ( Clients[BU] );
            Clients[BU] = _SearchBU
        );
        --IF(_SearchCompany = BLANK(); ALL(Clients[Company]); _FilterCompany);
        ALL ( Budget )
    )

It's basically the same but without the company stuff; the commented lines are when I tried to make the filter dynamic, based on the value of _SearchCompany, but it gives me the error "True/False expression without a specific column." (Changing ALL(Clients[Company]) to ALL(Clients) also gives an error)

Here's a screenshot of the result too:

Matrix Visual

I can't use LOOKUPVALUE since it's just a look up for one single value, and I need a sum for when multiple or all companies are selected.

Here's the link to the .pbix I prepared, it's not the original since it has sensitive data, but I made the replica as similar as possible and of course it has the same problem:

https://send.firefox.com/download/c47d098d71515f74/#Z2kBIp56uGl-Xlu3XVkBwA

I actually already tried asking for help on the PBI forum, but I only got an answer suggesting to use this formula:

BDG =
VAR _SearchDate =
EOMONTH ( SELECTEDVALUE ( Calendar[Date] ); 0 )
VAR _SearchBU =
SELECTEDVALUE ( Clients[BU] )
VAR _SearchCompany =
SELECTEDVALUE ( Clients[Company] )
VAR _FilterCompany =
FILTER ( ALLNOBLANKROW ( Clients[Company] ); Clients[Company] = _SearchCompany )
RETURN
    IF (
        _SearchCompany = BLANK ();
        CALCULATE (
            SUM ( Budget[ValueBDG] );
            FILTER ( ALLNOBLANKROW ( Calendar[Date] ); Calendar[Date] = _SearchDate );
            FILTER ( ALLNOBLANKROW ( Clients[BU] ); Clients[BU] = _SearchBU );
            ALL ( Clients[Company] );
            ALL ( Budget )
        );
        CALCULATE (
            SUM ( Budget[ValueBDG] );
            FILTER ( ALLNOBLANKROW ( Calendar[Date] ); Calendar[Date] = _SearchDate );
            FILTER ( ALLNOBLANKROW ( Clients[BU] ); Clients[BU] = _SearchBU );
            KEEPFILTERS ( _FilterCompany );
            ALL ( Budget )
        )
    )

But, while it appears to be working, it actually isn't because the value is correct when you either select one or all companies, but if you select only two out of three (talking about the dummy .pbix I provided, since I have more in the data I can't show) then the measure will still give you the sum of all companies' budget.

I also didn't want to write the formula like that if possible since I don't know if it's just the company filter giving me problems (in the real dataset), so if I had to write it like that for each combination of filters, it'd be a nightmare, if not impossible.

Plus, don't suggest that I duplicate the rows of the budget table: there are already tens of thousands of rows so multiplying them to have a row for each day of each year, for each client and for each company... it's just not viable.

Upvotes: 0

Views: 112

Answers (1)

greggyb
greggyb

Reputation: 3798

You should be good with the following:

Budget Daily = 
VAR CurrentMonth = SELECTEDVALUE ( 'Calendar'[YearMonth] )
RETURN
    CALCULATE (
        [EOM BDG],
        ALL ( 'Calendar' ),
        'Calendar'[YearMonth] = CurrentMonth
    )

In general, there's nothing special you need to do to preserve context. Evaluation context pretty much always remains the same unless you explicitly do something to change it. All we need to do is look at the month currently being plotted and override 'Calendar' context with that. If you need to extend this to contexts that include multiple months, rather than a single selection, you can just take VALUES ( 'Calendar'[YearMonth] ), rather than SELECTEDVALUE. The former represents all months in context. The latter is the single month in context, iff there is exactly one month in context.

To support this all, I added 'Calendar'[YearMonth], which I hope is self-explanatory, but for the sake of completeness is [Year] * 100 + [Month] to get a unique identifier for any month in time. This allows the measure to work when you load more than one year of data. [Month]=1 does not uniquely identify a specific January, but rather all Januaries. [YearMonth]=201901 identifies a specific January.

Upvotes: 1

Related Questions