Forum Discussion
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
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.
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
1 Reply
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.
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