cancel
Showing results for
Did you mean:
New Member

## Measure depending on filter selected

I have a filter selecttion (Prior Year, Current Year)

I have calculated the measure

Measure_sale_2021 = 2021_sale

(calculate(sum(FACT_INVOICE[InvoiceSales_USD]),YEAR(FACT_INVOICE[InvoiceDate]) = YEAR(TODAY())-2)

Measure_Sale-2022 = 2022_sale

(calculate(sum(FACT_INVOICE[InvoiceSales_USD]),YEAR(FACT_INVOICE[InvoiceDate]) = YEAR(TODAY())-1)

Measure_Sale_2023 = 2023_sale

(calculate(sum(FACT_INVOICE[InvoiceSales_USD]),YEAR(FACT_INVOICE[InvoiceDate]) = YEAR(TODAY()))

For filter Prior Year      Show Sale for Year 2021 as LY , Sale for Year 2022 as CY

For filter Current Year  show sale for Year 2022 as LY,  Sale for Year 2023  as CY

So I have a measure calculated

CY = SWITCH(VALUES('Date Filter'[FiscalDate Selection]),"PriorYear",[2022_sale],[2023_sale])

LY = SWITCH(VALUES('Date Filter'[FiscalDate Selection]),"PriorYear",[2021_sale],[2022_sale])

This works in a table where I have

Customer num, Cust name and Sale measure CY and LY

doenot work when the table has

Customer NUm, Cust Name, Product num, product Name CY and LY measure

gives me error

1 ACCEPTED SOLUTION
Community Support

Hi @karti1507 ,

According to the error message, it seems the returned data of visual exceed the maximum allowed size of '1000000' rows when you also apply the field [Product num] and [product Name] onto the visual. And there is a one-million row limit for data returned from cloud data sources with DirectQuery, which are any data sources that aren't on-premises. Please find the details in the below documentation.

One-million row limit

You can consider to create another two measures similar as below to replace the field [Product num] and [product Name] using the function CONCATENATEX, it will return less rows.

ConcatenateX in Power BI and DAX: Concatenate Values of a Column

``````Measure1 =
VAR _selcustnum =
SELECTEDVALUE ( 'Table'[Customer Num] )
RETURN
CONCATENATEX (
FILTER ( 'Table', 'Table'[Customer Num] = _selcustnum ),
'Table'[Product num],
","
)``````
``````Measure2 =
VAR _selcustnum =
SELECTEDVALUE ( 'Table'[Customer Num] )
RETURN
CONCATENATEX (
FILTER ( 'Table', 'Table'[Customer Num] = _selcustnum ),
'Table'[Product name],
","
)``````

Finally, you will get the result similar as below:

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support

Hi @karti1507 ,

According to the error message, it seems the returned data of visual exceed the maximum allowed size of '1000000' rows when you also apply the field [Product num] and [product Name] onto the visual. And there is a one-million row limit for data returned from cloud data sources with DirectQuery, which are any data sources that aren't on-premises. Please find the details in the below documentation.

One-million row limit

You can consider to create another two measures similar as below to replace the field [Product num] and [product Name] using the function CONCATENATEX, it will return less rows.

ConcatenateX in Power BI and DAX: Concatenate Values of a Column

``````Measure1 =
VAR _selcustnum =
SELECTEDVALUE ( 'Table'[Customer Num] )
RETURN
CONCATENATEX (
FILTER ( 'Table', 'Table'[Customer Num] = _selcustnum ),
'Table'[Product num],
","
)``````
``````Measure2 =
VAR _selcustnum =
SELECTEDVALUE ( 'Table'[Customer Num] )
RETURN
CONCATENATEX (
FILTER ( 'Table', 'Table'[Customer Num] = _selcustnum ),
'Table'[Product name],
","
)``````

Finally, you will get the result similar as below:

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.