Forum Discussion

IcingOnTheCake's avatar
IcingOnTheCake
Icon for Helper III rankHelper III
2 years ago

Do a select * for certain cases in DAX query

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
)

10 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      IcingOnTheCake
      Icon for Helper III rankHelper III
      2 years ago

      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

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    2 years ago

    Does this work?

     

    [...]
FILTER (
    VALUES ( 'Product'[Product Hierarchy Level 2 Code] ),
    PATHCONTAINS ( @P_PL2, 'Product'[Product Hierarchy Level 2 Code] )
        || TRIM ( @P_PL2 ) = ""
)
[...]
    • IcingOnTheCake's avatar
      IcingOnTheCake
      Icon for Helper III rankHelper III
      2 years ago

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

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      2 years ago

      AlexisOlson 

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

      • IcingOnTheCake's avatar
        IcingOnTheCake
        Icon for Helper III rankHelper III
        2 years ago

        Haha 😜 

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