topic Re: Do a select * for certain cases in DAX query in DAX Commands and Tips

Do a select * for certain cases in DAX query

IcingOnTheCake — Tue, 07 May 2024 09:06:17 GMT

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
)

Re: Do a select * for certain cases in DAX query

tamerj1 — Tue, 07 May 2024 11:40:20 GMT

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 )

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 )

Re: Do a select * for certain cases in DAX query

IcingOnTheCake — Tue, 07 May 2024 12:03:19 GMT

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

Re: Do a select * for certain cases in DAX query

tamerj1 — Tue, 07 May 2024 12:45:02 GMT

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

Re: Do a select * for certain cases in DAX query

IcingOnTheCake — Tue, 07 May 2024 13:15:03 GMT

no. unfortunatley that did not help

Re: Do a select * for certain cases in DAX query

tamerj1 — Tue, 07 May 2024 13:38:11 GMT

IcingOnTheCake
When nothing is selected what does your parameter return?

Re: Do a select * for certain cases in DAX query

IcingOnTheCake — Tue, 07 May 2024 14:50:13 GMT

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.

Re: Do a select * for certain cases in DAX query

AlexisOlson — Tue, 07 May 2024 15:55:20 GMT

Does this work?

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

Re: Do a select * for certain cases in DAX query

IcingOnTheCake — Tue, 07 May 2024 16:07:00 GMT

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

Re: Do a select * for certain cases in DAX query

tamerj1 — Tue, 07 May 2024 16:41:39 GMT

AlexisOlson

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

Re: Do a select * for certain cases in DAX query

IcingOnTheCake — Tue, 07 May 2024 17:25:20 GMT

Haha 😜

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