how to avoid bidirectional relationship in Power BI

[Anonymous]

I have two fact table and two dimensions table.

Table 1 : FactPrescriptionProjection
FactPrescriptionProjectionID	Contains unique values
PrescriptionProjectionCount	int value

Table 2 : FactPrescriptionPrduct
FactPrescriptionProjectionID	Contains duplicate values
ProductID	Contains duplicate values

Table 3 : DimProduct
DimProductID	Contains unique values
DimManufacturerID	Contains duplicate values

Table 4 : DimManufacturer
DimManufacturerID	Contains unique values
DimManufacturerName	varchar value

FactPrescriptionProjection table stores the records of doctor’s prescription data with unique id. A prescription record can have multiple medicine prescribed hence medicine is stored in another table (FactPrescriptionProduct Table) with FactPrescriptionProjection table’s unique id. And further products table is linked to DimProduct and same with manufacturer table. Please check data model for more detailed description of cardinality.

I want to get the prescription projection count against each manufacturer. Hence, I’m writing below DAX query.

Evaluate

SUMMARIZECOLUMNS(

    'DimManufacturer',

    'DimManufacturer'[ManufacturerName],

    "# Of Prescription", SUM('FactPrescriptionProjection'[PrescriptionProjection]),
)

I’m getting the correct result, but report is really working slow.

FactTablels have millions of records in it.

As far as I could determine the issue is in the cardinality between FactPrescriptionProjetcion  And FactPrescriptionProduct. As per current data model we need to use CROSS Filter to get the correct data which I guess causing it to slowdown.

Requesting your kind support to let me know how can we avoid bidirectional relationship in this current model  to get the result faster or any other method to have the issue addressed.

Link for the sample file. https://1drv.ms/u/s!AvZUqpXgHxIVzR87nuZuZngndT54?e=wboViJ

[v-yuta-msft]

@Anonymous ,

You can change the direction from both to single as below:

 

Community Support Team _ Jimmy Tao

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

[Anonymous]

Hi @v-yuta-msft 

Thanks for your reply.

I can set the filter direction to single.

but then the problem is i'm not getting the SUM value of 'FactPrescriptionProjection'[PrescriptionProjectionCount] against 'DimManufacturer'[DimManufacturerName].

Because 'DimManufacturer' Filters the 'FactPrescriptionProduct' And 'FactPrescriptionProduct' does not filter 'FactPrescriptionProjection'. Hence i had to use bi-directional relation for cross filtering.

Thanks.