topic Measure depending on filter selected in Developer

Measure depending on filter selected

karti1507 — Fri, 10 Mar 2023 16:44:27 GMT

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

Re: Measure depending on filter selected

Anonymous — Mon, 13 Mar 2023 03:31:12 GMT

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