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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Gregoire
Frequent Visitor

How Filtering by multiple typeValue for the same Type

Hello,

So this is my problem.
I have a data set like 
FactData : 
[ID BIGINT PRIMARY KEY,
CreateDate DATE,
CaseCount INT,
CaseValue DECIMAL(10, 2),
EffectiveDate DATE,
Type_A INT,
Type_B INT,
Type_C INT,
Type_D INT,
Type_E INT,
Type_F INT,
Type_G INT,
Type_H INT,
Type_I INT,
Type_J INT,
Type_K INT,
Type_L INT,
Type_M INT,
Type_N INT,
Type_O INT,
Type_P INT,
Type_Q INT,
Type_R INT,
Type_S INT,
Type_T INT,
Type_U INT,
Type_V INT,
Type_W INT,
Type_X INT,
Type_Y INT,
Type_Z INT]

And 
DimType : 
[ID INT PRIMARY KEY,
Type CHAR(1),
Value INT]

I need to filter my FactData with my DimType but my fact can have multiple type and I must can select multiple types.

Gregoire_0-1715592873411.png

 

This is my code but it not really quick, it take 15s to run across 5 million rows.

 

Filtered Quantity = 
VAR _SelectedTypes = VALUES('Type'[ID])

RETURN
IF(
    ISEMPTY(_SelectedTypes),
    [quantiter],
    CALCULATE(
        [quantiter],
        KEEPFILTERS(
            FILTER(
                'FactData', 
                (
					FactData[TypeA] IN _SelectedTypes ||
                    FactData[TypeB] IN _SelectedTypes ||
                    FactData[TypeC] IN _SelectedTypes ||
                    FactData[TypeD] IN _SelectedTypes ||
                    FactData[TypeE] IN _SelectedTypes ||
                    FactData[TypeF] IN _SelectedTypes ||
                    FactData[TypeG] IN _SelectedTypes ||
                    FactData[TypeH] IN _SelectedTypes ||
                    FactData[TypeI] IN _SelectedTypes ||
                    FactData[TypeJ] IN _SelectedTypes ||
                    FactData[TypeK] IN _SelectedTypes ||
                    FactData[TypeL] IN _SelectedTypes ||
                    FactData[TypeM] IN _SelectedTypes ||
                    FactData[TypeN] IN _SelectedTypes ||
                    FactData[TypeO] IN _SelectedTypes ||
                    FactData[TypeP] IN _SelectedTypes ||
                    FactData[TypeQ] IN _SelectedTypes ||
                    FactData[TypeR] IN _SelectedTypes ||
                    FactData[TypeS] IN _SelectedTypes ||
                    FactData[TypeT] IN _SelectedTypes ||
                    FactData[TypeU] IN _SelectedTypes ||
                    FactData[TypeV] IN _SelectedTypes ||
                    FactData[TypeW] IN _SelectedTypes ||
                    FactData[TypeX] IN _SelectedTypes ||
                    FactData[TypeY] IN _SelectedTypes ||
                    FactData[TypeZ] IN _SelectedTypes 
				)
            )
        )
    )
)

 


So how can I improve it?

I have made a pbix for example with a csv file for data.
But I don't know if I hoe to share it.

2 REPLIES 2
v-xiandat-msft
Community Support
Community Support

Hi @Gregoire ,

Utilize the Performance Analyzer in Power BI Desktop to identify which parts of your report are slow. This tool can help you pinpoint specific visuals or queries that are taking the most time to load.

Instead of using multiple OR conditions for each type, you can use a single condition that checks if the type exists in the _SelectedTypes variable. This will reduce the number of comparisons and improve performance.

Best Regards,

Xianda Tang

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

I already know is the measue how take long to resolve and the Performance Analyzer confirm it.

Instead of using multiple OR conditions for each type, you can use a single condition that checks if the type exists in the _SelectedTypes variable.


How can I do that?


Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.