Assistance Needed with DAX URL Construction for Child Report Navigation

[azeenk]

Dear Microsoft Community,

I hope this message finds you well.

I am currently working on a Power BI report where I need to navigate from a main report to a child report using a URL constructed with DAX. I am using the following DAX code to create the URL:

MainUrlPart =
VAR BaseURL = "https://app.powerbi.com/groups/702382c8-1b3f-434a-8a5d-5057e0bde7f3/reports/e9620c68-6719-48a8-9635-..."

VAR MinDate = MIN(qms_tij2x_precalculated_data[start_ts])
VAR StartDate = DATE(YEAR(MinDate), MONTH(MinDate), DAY(MinDate))
VAR EndDate = StartDate + 1

VAR DateFilter =
"qms_tij2x_stage_data/part_ts ge datetime'" & FORMAT(StartDate, "yyyy-MM-dd") & "T07:00:00'" &
" and qms_tij2x_stage_data/part_ts le datetime'" & FORMAT(EndDate, "yyyy-MM-dd") & "T06:59:59'"

-- Variables for Selected Values
VAR SiteValue = SELECTEDVALUE(qms_tij2x_precalculated_data[site_nm])
VAR ZoneValue = SELECTEDVALUE(qms_tij2x_precalculated_data[workcenter_zone_nm])
VAR LineValue = SELECTEDVALUE(qms_tij2x_precalculated_data[line_nm])
VAR EquipValue = SELECTEDVALUE(qms_tij2x_precalculated_data[equip_nm])
VAR GroupValue = SELECTEDVALUE(qms_tij2x_precalculated_data[group_nm])
VAR ProductValue = SELECTEDVALUE(qms_tij2x_precalculated_data[product_nm])
VAR PrefixValue = SELECTEDVALUE(qms_tij2x_precalculated_data[prefix_cd])
VAR ColorValue = SELECTEDVALUE(qms_tij2x_precalculated_data[color_cd])
VAR ParamValue = SELECTEDVALUE(qms_tij2x_precalculated_data[param_nm])
VAR MeasureToolValue = SELECTEDVALUE(qms_tij2x_precalculated_data[meas_tool_nm])

-- Filters in logical factory hierarchy order
VAR SiteFilter = IF(ISBLANK(SiteValue), " and qms_tij2x_stage_data/site_nm eq '" & SiteValue & "'", "")
VAR ZoneFilter = IF(ISBLANK(ZoneValue), " and qms_tij2x_stage_data/workcenter_zone_nm eq '" & ZoneValue & "'", "")
VAR LineFilter = IF(ISBLANK(LineValue), " and qms_tij2x_stage_data/line_nm eq '" & LineValue & "'", "")
VAR EquipFilter = IF(ISBLANK(EquipValue), " and qms_tij2x_stage_data/equip_nm eq '" & EquipValue & "'", "")
VAR GroupFilter = IF(ISBLANK(GroupValue), " and qms_tij2x_stage_data/group_nm eq '" & GroupValue & "'", "")
VAR ProductFilter = IF(ISBLANK(ProductValue), " and qms_tij2x_stage_data/product_nm eq '" & ProductValue & "'", "")
VAR PrefixFilter = IF(ISBLANK(PrefixValue), " and qms_tij2x_stage_data/prefix_cd eq '" & PrefixValue & "'", "")
VAR ColorFilter = IF(ISBLANK(ColorValue), " and qms_tij2x_stage_data/color_cd eq '" & ColorValue & "'", "")
VAR ParamFilter = IF(ISBLANK(ParamValue), " and qms_tij2x_stage_data/param_nm eq '" & ParamValue & "'", "")
VAR MeasureToolFilter = IF(ISBLANK(MeasureToolValue), " and qms_tij2x_stage_data/meas_tool_nm eq '" & MeasureToolValue & "'", "")

-- Final URL construction
VAR FinalURL =
BaseURL &
DateFilter &
GroupFilter &
MeasureToolFilter &
ZoneFilter &
LineFilter &
ParamFilter &
EquipFilter &
SiteFilter &
PrefixFilter

RETURN FinalUrl

I am using a table visual and adding this measure to it. Based on the available filtered row values, when I click on the link, it should show the records in the child report. However, I am facing an issue where the equip_nm field has blank values, and the URL construction does not work as expected. I have tried replacing the null values, but I am still encountering the same issue.

Has anyone worked on a similar scenario or have any suggestions on how to resolve this issue? Any help would be greatly appreciated.

Thank you in advance for your assistance.

Best regards,

Azeen

[v-pbandela-msft]

Hi @azeenk,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Thank you,
Pavan.

[v-pbandela-msft]

Hi @azeenk,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[v-pbandela-msft]

Hi @azeenk,

Thank you for reaching out in Microsoft Community Forum.

Thank you @DataNinja777 , @pankajnamekar25   for the helpful response.

As suggested by DataNinja777,pankainnamekar25.,  I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.

Please continue using Microsoft community forum.

Regards,
Pavan.

[DataNinja777]

Hi @azeenk ,

You're encountering an issue because the logic in your DAX formula for building the URL is backwards—you’re telling it to include a filter when the value is blank, which is the opposite of what you want. For example, this line:

VAR EquipFilter = IF(ISBLANK(EquipValue), " and qms_tij2x_stage_data/equip_nm eq '" & EquipValue & "'", "")

tries to insert a blank value into the URL when the equipment name is empty. That’s going to break the URL or make it filter incorrectly. Instead, you want to include the filter only if the value is present. To fix that, you should reverse the condition and write:

VAR EquipFilter = IF(NOT ISBLANK(EquipValue), " and qms_tij2x_stage_data/equip_nm eq '" & EquipValue & "'", "")

Repeat this pattern for all your other filters: check that the value is not blank before including the filter. This way, the URL will only contain valid filters based on available row context, and will skip over any fields that are blank—making the navigation from the main report to the child report reliable and clean. If you also have values with special characters (like spaces, slashes, or ampersands), you might eventually need to handle URL encoding, but for now, fixing the logic for blanks will resolve your immediate problem.

Best regards,

[pankajnamekar25]

Hello @azeenk 

Update all your condition in below DAX format

VAR EquipFilter = IF(NOT ISBLANK(EquipValue), " and qms_tij2x_stage_data/equip_nm eq '" & EquipValue & "'", "")

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[azeenk]

Hi @DataNinja777 , @pankajnamekar25 ,

I tried with that logic but still facing the same issue.

Could you please help me with this to resolve the issue.

Thanks in advance!

[DataNinja777]

Hi @azeenk,

The issue seems to be that the EquipValue is not being evaluated as truly blank, which can cause the filter to be added incorrectly. Even when using ISBLANK, if the value contains whitespace or unprintable characters, the check will fail. A more reliable approach is to use TRIM and explicitly compare against an empty string.

Update your EquipFilter logic as follows:

VAR CleanEquipValue = TRIM(EquipValue)

VAR EquipFilter =
    IF(
        NOT ISBLANK(CleanEquipValue) && CleanEquipValue <> "",
        " and qms_tij2x_stage_data/equip_nm eq '" & ENCODEURL(CleanEquipValue) & "'",
        ""
    )

This trims any extra spaces and ensures that only non-empty, non-null values are included. Repeat the same pattern for any other filters where similar issues may occur. Let me know if the issue still persists after this update.

Best regards,