Forum Discussion

chaitanya1's avatar
chaitanya1
Icon for Helper I rankHelper I
1 year ago
Solved

I am facing issue with direct query

error:table reports filters tables users which is from the same power bi data source or analysis services data source, through a path that exists outside of the data source:reports->role based access sheet-> users

I have templates source data "role", remaining all are direct query data users, views, dates, reports. all tables connnected to views. Role connected to reports, so when i filter data from organisation column in role table the data is filtering. when i mix columns date column from dates and viewsvalue from views table. Filtering taking so much time and i cannot connect users to role because reports and role connected it is already filtering users via reports and views connected. The report is very slow.

Please anyone help.

data protection
General Comment
Tutorial Requests
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago
    • Ensure that relationships between tables are set to single-direction filtering unless bi-directional filtering is necessary.
    • If possible, create an aggregation table for the "views" and "reports" data
    • If performance remains an issue, consider switching some tables (like "views" and "dates") to Import mode rather than Direct Query if the data does not need to be real-time.

    If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

3 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago
    • Ensure that relationships between tables are set to single-direction filtering unless bi-directional filtering is necessary.
    • If possible, create an aggregation table for the "views" and "reports" data
    • If performance remains an issue, consider switching some tables (like "views" and "dates") to Import mode rather than Direct Query if the data does not need to be real-time.

    If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

  • chaitanya1's avatar
    chaitanya1
    Icon for Helper I rankHelper I
    1 year ago

    Thanks Kedar_Pande  When i created bridgetable for tables it is not worked somehow when i start using your solution it is working. Now i am updating report.

    Thanks.

  • chaitanya1's avatar
    chaitanya1
    Icon for Helper I rankHelper I
    1 year ago

    Hi Community,

    I am facing issue to find out weekly report opens i found Daily Report Opens values by measure below, but i can't get right calculation to find Weekly Report Opens can anyone help for to find.

    Daily Report Opens =
    CALCULATE(
    ([viewscount]), // Sum of view counts
    FILTER(
    'Views',
    'Views'[Date] = MAX('BridgeTable_Simple'[Date]) && // Only today's date
    'Views'[ReportGuid] IN
    CALCULATETABLE(
    VALUES(Reports[ReportGuid]),
    'Role Based Access Sheet'[KPIs/Reports] = SELECTEDVALUE('Role Based Access Sheet'[Organisation])
    )
    )
    )
    I am trying, but it's coming like same values of daily report opens.

    Thanks.