Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
karti1507
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

karti1507_1-1678466566797.png

 

karti1507_0-1678466547119.png

 

 

 

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
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:

yingyinr_0-1678678240854.png

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.

View solution in original post

1 REPLY 1
v-yiruan-msft
Community Support
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:

yingyinr_0-1678678240854.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.