Do a select * for certain cases in DAX query

[IcingOnTheCake]

Dear Experts, 

I need help for the bold red part please.
Actually I would like to do a select all. Or to talk in sql:

'Product'[Product Hierarchy Level 2 Code] = *

How can I achieve that please?

Thank you

EVALUATE
FILTER (
SUMMARIZECOLUMNS (
'Customer Sell-To'[Sell-To Customer],
'Date'[Calendar Year],
'Date'[Calendar Quarter],
'Date'[Month],
'Product'[Product Hierarchy Level 1],
'Product'[Product Hierarchy Level 2],
'Product'[Product],
FILTER (
VALUES ( 'Customer Sell-To'[Sell-To Customer Code] ),
'Customer Sell-To'[Sell-To Customer Code] = @P_Customer
),
FILTER (
VALUES ( 'Product'[Product Hierarchy Level 1 Code] ),
PATHCONTAINS ( @P_PL1, 'Product'[Product Hierarchy Level 1 Code] )
),
FILTER (
VALUES ( 'Product'[Product Hierarchy Level 2 Code] ),
IF (
NOT ( ISBLANK ( TRIM ( @P_PL2 ) ) ),
PATHCONTAINS ( @P_PL2, 'Product'[Product Hierarchy Level 2 Code] ),
ALL ( 'Product'[Product Hierarchy Level 2 Code] )
)
),
"Sales Amt EUR CY",
CALCULATE (
[Sales Amt EUR],
FILTER (
VALUES ( 'Date'[Calendar Year] ),
'Date'[Calendar Year] = VALUE ( @P_CY )
)
),
"Qty CY",
CALCULATE (
[Quantity],
FILTER (
VALUES ( 'Date'[Calendar Year] ),
'Date'[Calendar Year] = VALUE ( @P_CY )
)
)
),
[Sales Amt EUR CY] <> 0
|| [Qty CY] <> 0
)

[AlexisOlson]

Does this work?

[...]
FILTER (
    VALUES ( 'Product'[Product Hierarchy Level 2 Code] ),
    PATHCONTAINS ( @P_PL2, 'Product'[Product Hierarchy Level 2 Code] )
        || TRIM ( @P_PL2 ) = ""
)
[...]

[tamerj1]

@AlexisOlson 

I've been searching for what the heck does the empty parameter in paginated reports actually return 😅

[IcingOnTheCake]

Haha 😜 

but although we know now, I still dont get why do not get results 😕

[IcingOnTheCake]

Thanks and "good idea"! It runs. But then again "no rows found" 😞

[tamerj1]

Hi @IcingOnTheCake 

Maybe

EVALUATE
FILTER (
    SUMMARIZECOLUMNS (
        'Customer Sell-To'[Sell-To Customer],
        'Date'[Calendar Year],
        'Date'[Calendar Quarter],
        'Date'[Month],
        'Product'[Product Hierarchy Level 1],
        'Product'[Product Hierarchy Level 2],
        'Product'[Product],
        FILTER (
            VALUES ( 'Customer Sell-To'[Sell-To Customer Code] ),
            'Customer Sell-To'[Sell-To Customer Code] = @P_Customer
        ),
        FILTER (
            VALUES ( 'Product'[Product Hierarchy Level 1 Code] ),
            PATHCONTAINS ( @P_PL1, 'Product'[Product Hierarchy Level 1 Code] )
        ),
        FILTER (
            VALUES ( 'Product'[Product Hierarchy Level 2 Code] ),
            IF (
                NOT ( ISBLANK ( TRIM ( @P_PL2 ) ) ),
                PATHCONTAINS ( @P_PL2, 'Product'[Product Hierarchy Level 2 Code] ),
                TRUE
            )
        ),
        "Sales Amt EUR CY",
            CALCULATE (
                [Sales Amt EUR],
                FILTER (
                    VALUES ( 'Date'[Calendar Year] ),
                    'Date'[Calendar Year] = VALUE ( @P_CY )
                )
            ),
        "Qty CY",
            CALCULATE (
                [Quantity],
                FILTER (
                    VALUES ( 'Date'[Calendar Year] ),
                    'Date'[Calendar Year] = VALUE ( @P_CY )
                )
            )
    ),
    [Sales Amt EUR CY] <> 0
        || [Qty CY] <> 0
)

or

EVALUATE
FILTER (
    SUMMARIZECOLUMNS (
        'Customer Sell-To'[Sell-To Customer],
        'Date'[Calendar Year],
        'Date'[Calendar Quarter],
        'Date'[Month],
        'Product'[Product Hierarchy Level 1],
        'Product'[Product Hierarchy Level 2],
        'Product'[Product],
        FILTER (
            VALUES ( 'Customer Sell-To'[Sell-To Customer Code] ),
            'Customer Sell-To'[Sell-To Customer Code] = @P_Customer
        ),
        FILTER (
            VALUES ( 'Product'[Product Hierarchy Level 1 Code] ),
            PATHCONTAINS ( @P_PL1, 'Product'[Product Hierarchy Level 1 Code] )
        ),
        FILTER (
            VALUES ( 'Product'[Product Hierarchy Level 2 Code] ),
            OR (
                NOT ( ISBLANK ( TRIM ( @P_PL2 ) ) )
                    && PATHCONTAINS ( @P_PL2, 'Product'[Product Hierarchy Level 2 Code] ),
                ISBLANK ( TRIM ( @P_PL2 ) )
            )
        ),
        "Sales Amt EUR CY",
            CALCULATE (
                [Sales Amt EUR],
                FILTER (
                    VALUES ( 'Date'[Calendar Year] ),
                    'Date'[Calendar Year] = VALUE ( @P_CY )
                )
            ),
        "Qty CY",
            CALCULATE (
                [Quantity],
                FILTER (
                    VALUES ( 'Date'[Calendar Year] ),
                    'Date'[Calendar Year] = VALUE ( @P_CY )
                )
            )
    ),
    [Sales Amt EUR CY] <> 0
        || [Qty CY] <> 0
)

[IcingOnTheCake]

Thank you very much @tamerj1 for your answer.

The result for your suggestions is, that it filters correct, if there is a value for @P_PL2

But in case the user did not add any parameter, the result is "No rows found" whereas it should bring ALL AVAILABLE PL2 Values. (PL2 = *)
Any other idea? Thank you

[tamerj1]

@IcingOnTheCake 
Try to replace NOT ( ISBLANK ( TRIM ( @P_PL2 ) ) ) with TRIM ( @P_PL2 ) <> ""

[IcingOnTheCake]

no. unfortunatley that did not help

[tamerj1]

@IcingOnTheCake 
When nothing is selected what does your parameter return?

[IcingOnTheCake]

As null is not allowed for this slicer, it should be BLANK.
In Report Builder, what does a Slicer return, if nothing is selected?
Normally it's blank.