Create a filtered summarized table that contains unions

[jcampbell474]

I'm pretty new to PowerBI (DAX).  I do have a strong Qlikview background, just working a little w/PBI.

My date field is YYYYMM.  I'm trying to create a slicer to select the current month (CM), previous month (PM), rollilng 3-months (R3M), and rolling 6-months (R6M).  After finding that I can't use expressions in the slicer, it became apparent that a summary table is needed.  Again, I'm new to PBI.  It's coming along slowly and I think now is the time to reach out for some assistance.

I've tried the FILTER in several different places, not sure where the optimal placement is.  Am I going about this in the right way?

Here's my code (note: fiscalyearmonthMAX is a measured field):

SummaryTable =
UNION(
SUMMARIZE('Workgroup CPC'
, 'Workgroup CPC'[Department]
, 'Workgroup CPC'[Location]
, "Period","CM"
, "Calls Answered Summary",CALCULATE(SUM('Workgroup CPC'[CallsAnswered]),FILTER('Workgroup CPC',MAX('Workgroup CPC'[fiscalyearmonth]) = [fiscalyearmonthMAX]))
, "Handle Time Mins Summary", sum('Workgroup CPC'[HandleTimeMins])
, "Handle Time Cost Summary", sum('Workgroup CPC'[HandleTimeCost])
, "Available Allocated Mins Summary", sum('Workgroup CPC'[AvailableAllocatedMinutes])
, "Available Allocated Cost Summary", sum('Workgroup CPC'[AvailableAllocatedCost])),
SUMMARIZE('Workgroup CPC'
, 'Workgroup CPC'[Department]
, 'Workgroup CPC'[Location]
, "Period","PM"
, "Calls Answered Summary",CALCULATE(SUM('Workgroup CPC'[CallsAnswered]),FILTER('Workgroup CPC',MAX('Workgroup CPC'[fiscalyearmonth])-1 = [fiscalyearmonthMAX]-1))
, "Handle Time Mins Summary", sum('Workgroup CPC'[HandleTimeMins])
, "Handle Time Cost Summary", sum('Workgroup CPC'[HandleTimeCost])
, "Available Allocated Mins Summary", sum('Workgroup CPC'[AvailableAllocatedMinutes])
, "Available Allocated Cost Summary", sum('Workgroup CPC'[AvailableAllocatedCost]))) 

Thanks in advance for any/all help.

Jason

[jcampbell474]

Update:  I think I have two of the four periods using the code below.  

- Not sure how to get the prior 3-months (YYYYMM) and prior 6-months (also YYYYMM).  

- The expression seems rather lengthy.  Is there any optimization I can do?

SummaryTable =
UNION (
    SUMMARIZE (
        'Workgroup CPC',
        'Workgroup CPC'[Department],
        'Workgroup CPC'[Location],
        "Period", "CM",
        "Calls Answered Summary", CALCULATE (
            SUMX (
                'Workgroup CPC',
                'Workgroup CPC'[CallsAnswered]
            ),
            FILTER (
                'Workgroup CPC',
                CONTAINS (
                    VALUES ( 'Workgroup CPC'[fiscalyearmonth] ),
                    'Workgroup CPC'[fiscalyearmonth], [fiscalyearmonthMAX]
                )
            )
        ),
        "Handle Time Mins Summary", CALCULATE (
            SUMX (
                'Workgroup CPC',
                'Workgroup CPC'[HandleTimeMins]
            ),
            FILTER (
                'Workgroup CPC',
                CONTAINS (
                    VALUES ( 'Workgroup CPC'[fiscalyearmonth] ),
                    'Workgroup CPC'[fiscalyearmonth], [fiscalyearmonthMAX]
                )
            )
        ),
        "Handle Time Cost Summary", CALCULATE (
            SUMX (
                'Workgroup CPC',
                'Workgroup CPC'[HandleTimeCost]
            ),
            FILTER (
                'Workgroup CPC',
                CONTAINS (
                    VALUES ( 'Workgroup CPC'[fiscalyearmonth] ),
                    'Workgroup CPC'[fiscalyearmonth], [fiscalyearmonthMAX]
                )
            )
        ),
        "Available Allocated Mins Summary", CALCULATE (
            SUMX (
                'Workgroup CPC',
                'Workgroup CPC'[AvailableAllocatedMinutes]
            ),
            FILTER (
                'Workgroup CPC',
                CONTAINS (
                    VALUES ( 'Workgroup CPC'[fiscalyearmonth] ),
                    'Workgroup CPC'[fiscalyearmonth], [fiscalyearmonthMAX]
                )
            )
        ),
        "Available Allocated Cost Summary", CALCULATE (
            SUMX (
                'Workgroup CPC',
                'Workgroup CPC'[AvailableAllocatedCost]
            ),
            FILTER (
                'Workgroup CPC',
                CONTAINS (
                    VALUES ( 'Workgroup CPC'[fiscalyearmonth] ),
                    'Workgroup CPC'[fiscalyearmonth], [fiscalyearmonthMAX]
                )
            )
        )
    ),
    SUMMARIZE (
        'Workgroup CPC',
        'Workgroup CPC'[Department],
        'Workgroup CPC'[Location],
        "Period", "PM",
        "Calls Answered Summary", CALCULATE (
            SUMX (
                'Workgroup CPC',
                'Workgroup CPC'[CallsAnswered]
            ),
            FILTER (
                'Workgroup CPC',
                CONTAINS (
                    VALUES ( 'Workgroup CPC'[fiscalyearmonth] ),
                    'Workgroup CPC'[fiscalyearmonth], [fiscalyearmonthPM]
                )
            )
        ),
        "Handle Time Mins Summary", CALCULATE (
            SUMX (
                'Workgroup CPC',
                'Workgroup CPC'[HandleTimeMins]
            ),
            FILTER (
                'Workgroup CPC',
                CONTAINS (
                    VALUES ( 'Workgroup CPC'[fiscalyearmonth] ),
                    'Workgroup CPC'[fiscalyearmonth], [fiscalyearmonthPM]
                )
            )
        ),
        "Handle Time Cost Summary", CALCULATE (
            SUMX (
                'Workgroup CPC',
                'Workgroup CPC'[HandleTimeCost]
            ),
            FILTER (
                'Workgroup CPC',
                CONTAINS (
                    VALUES ( 'Workgroup CPC'[fiscalyearmonth] ),
                    'Workgroup CPC'[fiscalyearmonth], [fiscalyearmonthPM]
                )
            )
        ),
        "Available Allocated Mins Summary", CALCULATE (
            SUMX (
                'Workgroup CPC',
                'Workgroup CPC'[AvailableAllocatedMinutes]
            ),
            FILTER (
                'Workgroup CPC',
                CONTAINS (
                    VALUES ( 'Workgroup CPC'[fiscalyearmonth] ),
                    'Workgroup CPC'[fiscalyearmonth], [fiscalyearmonthPM]
                )
            )
        ),
        "Available Allocated Cost Summary", CALCULATE (
            SUMX (
                'Workgroup CPC',
                'Workgroup CPC'[AvailableAllocatedCost]
            ),
            FILTER (
                'Workgroup CPC',
                CONTAINS (
                    VALUES ( 'Workgroup CPC'[fiscalyearmonth] ),
                    'Workgroup CPC'[fiscalyearmonth], [fiscalyearmonthPM]
                )
            )
        )
    )
)

[LivioLanzo]

@jcampbell474

would you be able to post a sample dataset?

[jcampbell474]

I don't have sample data readily available.  I did post an update to my expression, but the moderator removed it as spam.  I sent a note asking that it be reinstated.  Haven't heard back...

[LivioLanzo]

Hi @jcampbell474

I have taken a look at your formula and in Power BI you do not need to create such tables. You can take advantage of measures and create one measure per calculation, i.e. one for Last Year Sales, one for Year to Date Sales etc and display the results in a matrix or chart

Wondering if that is how it is done in Qlik?

[jcampbell474]

Thank you for the reply, Livio.

A requirement is being able to select current month, previous month, rolling 3-months, and rolling 6-months.  To my knowledge, this can only be done via a slicer.  

I do not have any tables in the dashboard - just charts.  In other words, a measure is not needed.  If a slicer can contain a measure to make selections, I'll take it!

My background is in Qlik so it feels much easier to me.  You create buttons that can have MANY custom (NATIVE) actions assigned to them.  One action is Toggle Select.  Here is an example of quickly using a button to toggle between all dates and previous 3-months: ='=(min(year(QuoteDateKey))*12+min(month(QuoteDateKey)))>CurrYr*12+CurrMo-3'. 

Lastly, knowing that I need to be able to select one of the four date groupings, is there a way to do it with a slicer without also having a summary table?  

Thanks!

Jason

[LivioLanzo]

@jcampbell474

with an offline parameter table you are able to have a slicer by which you can choose the measure to display:

look at the second part of this article

https://www.sqlbi.com/articles/optimizing-if-and-switch-expressions-using-variables/

[jcampbell474]

Livio, again, thank you.  I wasn't able to use info at the link, but have made a little progress. 

Hoping someone can help me with this.  I get the number I need in a card, but it will not work if I use an object w/a dimension.  Why?  What can I do to fix it?

_test =
VAR vPM =
    VALUE (
        MAX ( 'Workgroup CPC'[fiscalyearmonth] ) - 1
    )
RETURN
    CALCULATE (
        SUMX (
            'Workgroup CPC',
            'Workgroup CPC'[CallsAnswered]
        ),
        FILTER (
            'Workgroup CPC',
            'Workgroup CPC'[fiscalyearmonth] = vPM
        )
    )

TIA,

Jason