DAX Flag in Matrix Visual - Multiple levels of analysis

Clifford Piehl 0
Viewed 18

I replicated an issue I am having with the 'Adventure Works DW 2020' pbix file, so if my analysis seems a little out of context, please understand this example is not the true data I am working with. The pbix I used can be downloaded here:

https://drive.google.com/file/d/1vn6CluiE5rrAF3UjYPh5ejb93H2JX6IX/view?usp=sharing

My goal is to create a measure that can flag the subset of records that I want to use for a matrix visual.

I created the following measure with notes in the syntax:

VAR TABLEVAR =
SELECTCOLUMNS(
    FILTER( 
        SUMMARIZE( 
            CALCULATETABLE(Sales/*Apply several filters to Sales table*/
                ,NOT Sales[CustomerKey] = -1
                ,Sales[orderdatekey] > 20180731
                ,Sales[orderdatekey] < 20190601
            )
            ,[CustomerKey]/*Count the number of products per customer*/
            ,"Count",COUNT(Sales[ProductKey])
        )
        ,[Count] > 1/*Only keep customers that bought more than 1 product*/
    )
    ,[CustomerKey] /*Select the identifiers of the desired customers*/
)
RETURN
{
SWITCH(TRUE()
    ,SELECTEDVALUE(Sales[CustomerKey]) IN TABLEVAR/*Flag the customers that were identified in the previous table*/
    ,1,BLANK()
)
}

Now, in the PowerBI Matrix visual, this seems to work at first: enter image description here

I had successfully flagged the desired output. Now I just have to filter for the 'Analysis' measure to be 'Not Blank', but then this happens: enter image description here

Now removing that filter and going down a level: enter image description here

So you see, the measure does not evaluate at the record level of the table. Does anyone understand the concept I am missing here? I have tried all kinds of different measures but it all comes down to the same problem about flagging different levels of analysis.

Ideally, the output would only include the following(circled in green): enter image description here

These are the records that are within the date filters I put into the CALCULATETABLE() arguments.

Any help or insight with this problem would be greatly appreciated. Thank you

daxstudio dax powerbi-desktop powerbi
1 Answers

I'm not 100% clear what you're trying to do but please try the following and see if it helps.

Analysis = 
VAR TABLEVAR =
SELECTCOLUMNS(
    FILTER(
        SUMMARIZE(
            CALCULATETABLE(Sales
                ,NOT Sales[CustomerKey] = -1
                ,Sales[orderdatekey] > 20180731
                ,Sales[orderdatekey] < 20190601,
                REMOVEFILTERS()
            )
            ,[CustomerKey]
            ,"Count",COUNT(Sales[ProductKey])
        )
        ,[Count] > 1
    )
    ,[CustomerKey]
)
RETURN

//CONCATENATEX(TABLEVAR, [CustomerKey], ",")

SWITCH(TRUE()
    ,SELECTEDVALUE(Sales[CustomerKey]) IN TABLEVAR
    ,1,BLANK()
)
userjpYe10
Related