Forum Discussion

askme1217's avatar
askme1217
Frequent Visitor
1 year ago
Solved

Help : DAX measure

i am creating a measure to pass filters via url.  For single filters its working but i need to make it more dynmaic since my report has over 10 slicers. so here is the url. and slicers  like  slicer1,slicer2, slicer3,slicer4,slicer5, slicer6,slicer7,slicer8, slicer9,slicer10

slicers can be selected as, All, single value, or multiple values.  i need to construct below url dynamically based on above slicer selections. when slicer is not filtered (or All) i dont want to pass any filter but when single or multiple, i want to pass

like ex: if slicer1 and slicer2 are selected and rest of them are not filtered, i want

url =   https://app.powerbi.com/xxxxxxxx?experience=power-bi + slicer1+slicer2 

so basically, only when a slicer is filtered, pass it in url otherwise no. SInce i have 10 slicers, how can i write it dynamically.?

 

Need Help
  • MFelix's avatar
    MFelix
    1 year ago

    Hi askme1217 ,

     

    For this you need to use the syntax of the table/column and then using a sintax with a in statement. and for each table add an AND.

     

    So you would get something similar to: 

    url =   https://app.powerbi.com/xxxxxxxx?experience=power-bi ?filter=Table1/Column1 in ('data1', 'data2') and Table2/Column1 in ('data3', 'data4')

    You can check the details on this link below:
    https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters#filter-on-multiple-fields
    To get the values for the DAX syntax you would need use a concatanex of the VALUES of your field values that are selected on the slicer.

     

    So that would be something similar to:

    filter Values =
VAR slicer1 =
    CONCATENATEX (
        ADDCOLUMNS (
            VALUES ( 'Table1'[Column1] ),
            "FilterColumn",
                "'" & 'Table1'[Column1] & "'"
        ),
        [FilterColumn],
        ","
    )
VAR slicer2 =
    CONCATENATEX (
        ADDCOLUMNS (
            VALUES ( 'Table2'[Column1] ),
            "FilterColumn",
                "'" & 'Table2'[Column1] & "'"
        ),
        [FilterColumn],
        ","
    )
RETURN
    "https://app.powerbi.com/xxxxxxxx?experience=power-bi & ?filter= Table1/Column1 in (" & slicer1 & ") and Table2/Column2 in (" & slicer2 & ")"

    Be carefull that this type of measure can return a error in text lenght. you may also need to adjust the full detail of the URL and the way the slicers works in the concatanex

     

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

    Hi askme1217 ,

     

    For this you need to use the syntax of the table/column and then using a sintax with a in statement. and for each table add an AND.

     

    So you would get something similar to: 

    url =   https://app.powerbi.com/xxxxxxxx?experience=power-bi ?filter=Table1/Column1 in ('data1', 'data2') and Table2/Column1 in ('data3', 'data4')

    You can check the details on this link below:
    https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters#filter-on-multiple-fields
    To get the values for the DAX syntax you would need use a concatanex of the VALUES of your field values that are selected on the slicer.

     

    So that would be something similar to:

    filter Values =
VAR slicer1 =
    CONCATENATEX (
        ADDCOLUMNS (
            VALUES ( 'Table1'[Column1] ),
            "FilterColumn",
                "'" & 'Table1'[Column1] & "'"
        ),
        [FilterColumn],
        ","
    )
VAR slicer2 =
    CONCATENATEX (
        ADDCOLUMNS (
            VALUES ( 'Table2'[Column1] ),
            "FilterColumn",
                "'" & 'Table2'[Column1] & "'"
        ),
        [FilterColumn],
        ","
    )
RETURN
    "https://app.powerbi.com/xxxxxxxx?experience=power-bi & ?filter= Table1/Column1 in (" & slicer1 & ") and Table2/Column2 in (" & slicer2 & ")"

    Be carefull that this type of measure can return a error in text lenght. you may also need to adjust the full detail of the URL and the way the slicers works in the concatanex