topic How Filtering by multiple typeValue for the same Type in Report Server

How Filtering by multiple typeValue for the same Type

Gregoire — Mon, 13 May 2024 11:40:26 GMT

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.

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.

Re: How Filtering by multiple typeValue for the same Type

Anonymous — Tue, 14 May 2024 05:51:45 GMT

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.

Re: How Filtering by multiple typeValue for the same Type

Gregoire — Tue, 14 May 2024 07:04:28 GMT

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?