Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Help with Drillthrough Link – Child Report

 

Hi Team,

I'm currently working on a Power BI report that includes a drillthrough feature to navigate from a main report to a child report. I'm using the following DAX expression to generate the drillthrough URL:

 

DAX

MainUrlPart =
VAR BaseURL = "https://app.powerbi.com/groups/702382c8-1b3f-434a-8a5d-5057e0bde7f3/reports/e9620c68-6719-48a8-9635-a2a2e9b3bfb8/ReportSection?filter="
VAR DateFilter =
CONCATENATE(
"qms_tij2x_precalculated_data/start_ts ge datetime'",
CONCATENATE(
FORMAT(MIN(qms_tij2x_precalculated_data[start_ts]), "yyyy-MM-dd"),
"T00:00:00' "
)
) &
CONCATENATE(
"and qms_tij2x_precalculated_data/part_ts lt datetime'",
CONCATENATE(
FORMAT(
DATE(
YEAR(MAX(qms_tij2x_precalculated_data[start_ts])),
MONTH(MAX(qms_tij2x_precalculated_data[start_ts])),
DAY(MAX(qms_tij2x_precalculated_data[start_ts])) + 1
),
"yyyy-MM-dd"
),
"T00:00:00' "
)
)
VAR Filters =
CONCATENATE(
"and qms_tij2x_precalculated_data/group_nm eq '",
CONCATENATE(
SELECTEDVALUE(qms_tij2x_precalculated_data[group_nm]),
"' "
)
) &
CONCATENATE(
"and qms_tij2x_precalculated_data/param_nm eq '",
CONCATENATE(
SELECTEDVALUE(qms_tij2x_precalculated_data[param_nm]),
"' "
)
) &
CONCATENATE(
"and qms_tij2x_precalculated_data/line_nm eq '",
CONCATENATE(
SELECTEDVALUE(qms_tij2x_precalculated_data[line_nm]),
"' "
)
) &
CONCATENATE(
"and qms_tij2x_precalculated_data/product_nm eq '",
CONCATENATE(
SELECTEDVALUE(qms_tij2x_precalculated_data[product_nm]),
"' "
)
) &
CONCATENATE(
"and qms_tij2x_precalculated_data/prefix_cd eq '",
CONCATENATE(
SELECTEDVALUE(qms_tij2x_precalculated_data[prefix_cd]),
"' "
)
) &
CONCATENATE(
"and qms_tij2x_precalculated_data/workcenter_zone_nm eq '",
CONCATENATE(
SELECTEDVALUE(qms_tij2x_precalculated_data[workcenter_zone_nm]),
"' "
)
) &
CONCATENATE(
"and qms_tij2x_precalculated_data/site_nm eq '",
CONCATENATE(
SELECTEDVALUE(qms_tij2x_precalculated_data[site_nm]),
"' "
)
) &
CONCATENATE(
"and qms_tij2x_precalculated_data/color_cd eq '",
CONCATENATE(
SELECTEDVALUE(qms_tij2x_precalculated_data[color_cd]),
"' "
)
)
VAR FullURL = CONCATENATE(BaseURL, CONCATENATE(DateFilter, Filters))

RETURN FullURL


There is a column called group_name that contains values like PA, NENS, and HCI.
In the main report, I have applied a filter to show only PA. When I click and navigate to the child report for PA, it works fine.

However, when I change the selection to NENS or HCI in the main report and then click to drill through, the child report opens but shows blank visuals – even though data exists for those groups.

I have confirmed that the group_name field is the same in both the main and child report.
Can someone help me identify what might be causing the child report to go blank for NENS and HCI?

 

Thanks in advance!

 

 

Regards,

Azeen

Interesting Links
Need Help
Show and Tell
  • v-achippa's avatar
    v-achippa
    1 year ago

    Hi Anonymous,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    The issue is likely caused by missing URL encoding or case sensitivity in your drillthrough URL filters:

    • When you drill through using group_nm = NENS or HCI, the generated URL might break if the value contains special characters like spaces or if it is not encoded properly. This cause the child report may receive an invalid or incomplete filter, leading to blank visuals.
    • Also please ensure that the group_nm field is included in the Drillthrough filters section of your child report. This is needed for the drillthrough to work correctly, as it ensures that the necessary filter is passed from the main report.
    • One quick fix is like wrap all the “SELECTEDVALUE(...)” expressions with ENCODEURL() in your DAX for example like
      ENCODEURL(SELECTEDVALUE(qms_tij2x_precalculated_data[group_nm]))

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thanks and regards,

    Anjan Kumar Chippa

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

     

    Hi Team,

    I'm currently working on a Power BI report that includes a drillthrough feature to navigate from a main report to a child report. I'm using the following DAX expression to generate the drillthrough URL:

     

    DAX

    MainUrlPart =
    VAR BaseURL = "https://app.powerbi.com/groups/702382c8-1b3f-434a-8a5d-5057e0bde7f3/reports/e9620c68-6719-48a8-9635-a2a2e9b3bfb8/ReportSection?filter="
    VAR DateFilter =
    CONCATENATE(
    "qms_tij2x_precalculated_data/start_ts ge datetime'",
    CONCATENATE(
    FORMAT(MIN(qms_tij2x_precalculated_data[start_ts]), "yyyy-MM-dd"),
    "T00:00:00' "
    )
    ) &
    CONCATENATE(
    "and qms_tij2x_precalculated_data/part_ts lt datetime'",
    CONCATENATE(
    FORMAT(
    DATE(
    YEAR(MAX(qms_tij2x_precalculated_data[start_ts])),
    MONTH(MAX(qms_tij2x_precalculated_data[start_ts])),
    DAY(MAX(qms_tij2x_precalculated_data[start_ts])) + 1
    ),
    "yyyy-MM-dd"
    ),
    "T00:00:00' "
    )
    )
    VAR Filters =
    CONCATENATE(
    "and qms_tij2x_precalculated_data/group_nm eq '",
    CONCATENATE(
    SELECTEDVALUE(qms_tij2x_precalculated_data[group_nm]),
    "' "
    )
    ) &
    CONCATENATE(
    "and qms_tij2x_precalculated_data/param_nm eq '",
    CONCATENATE(
    SELECTEDVALUE(qms_tij2x_precalculated_data[param_nm]),
    "' "
    )
    ) &
    CONCATENATE(
    "and qms_tij2x_precalculated_data/line_nm eq '",
    CONCATENATE(
    SELECTEDVALUE(qms_tij2x_precalculated_data[line_nm]),
    "' "
    )
    ) &
    CONCATENATE(
    "and qms_tij2x_precalculated_data/product_nm eq '",
    CONCATENATE(
    SELECTEDVALUE(qms_tij2x_precalculated_data[product_nm]),
    "' "
    )
    ) &
    CONCATENATE(
    "and qms_tij2x_precalculated_data/prefix_cd eq '",
    CONCATENATE(
    SELECTEDVALUE(qms_tij2x_precalculated_data[prefix_cd]),
    "' "
    )
    ) &
    CONCATENATE(
    "and qms_tij2x_precalculated_data/workcenter_zone_nm eq '",
    CONCATENATE(
    SELECTEDVALUE(qms_tij2x_precalculated_data[workcenter_zone_nm]),
    "' "
    )
    ) &
    CONCATENATE(
    "and qms_tij2x_precalculated_data/site_nm eq '",
    CONCATENATE(
    SELECTEDVALUE(qms_tij2x_precalculated_data[site_nm]),
    "' "
    )
    ) &
    CONCATENATE(
    "and qms_tij2x_precalculated_data/color_cd eq '",
    CONCATENATE(
    SELECTEDVALUE(qms_tij2x_precalculated_data[color_cd]),
    "' "
    )
    )
    VAR FullURL = CONCATENATE(BaseURL, CONCATENATE(DateFilter, Filters))

    RETURN FullURL


    There is a column called group_name that contains values like PA, NENS, and HCI.
    In the main report, I have applied a filter to show only PA. When I click and navigate to the child report for PA, it works fine.

    However, when I change the selection to NENS or HCI in the main report and then click to drill through, the child report opens but shows blank visuals – even though data exists for those groups.

    I have confirmed that the group_name field is the same in both the main and child report.
    Can someone help me identify what might be causing the child report to go blank for NENS and HCI?

     

    Thanks in advance!

     

     

    Regards,

    Azeen

  • v-achippa's avatar
    v-achippa
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi Anonymous,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    The issue is likely caused by missing URL encoding or case sensitivity in your drillthrough URL filters:

    • When you drill through using group_nm = NENS or HCI, the generated URL might break if the value contains special characters like spaces or if it is not encoded properly. This cause the child report may receive an invalid or incomplete filter, leading to blank visuals.
    • Also please ensure that the group_nm field is included in the Drillthrough filters section of your child report. This is needed for the drillthrough to work correctly, as it ensures that the necessary filter is passed from the main report.
    • One quick fix is like wrap all the “SELECTEDVALUE(...)” expressions with ENCODEURL() in your DAX for example like
      ENCODEURL(SELECTEDVALUE(qms_tij2x_precalculated_data[group_nm]))

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thanks and regards,

    Anjan Kumar Chippa

    • v-achippa's avatar
      v-achippa
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi Anonymous,

       

      As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
      If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

       

      Thanks and regards,

      Anjan Kumar Chippa

      • v-achippa's avatar
        v-achippa
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi @azeenk,

         

        We wanted to kindly follow up to check if the solution I have provided for the issue worked.
        If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

         

        Thanks and regards,

        Anjan Kumar Chippa

    • v-achippa's avatar
      v-achippa
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi Anonymous,

       

      As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
      If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

       

      Thanks and regards,

      Anjan Kumar Chippa