Measure depending on filter selected

[karti1507]

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

[v-yiruan-msft · Accepted solution]

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