Forum Discussion

viswaaa's avatar
viswaaa
Icon for Helper IV rankHelper IV
9 months ago
Solved

Slicer on measures

Hi All,

I am connecting to power bi symantic model feature usgae and adoption to create a report and I have added 2 tables workspaces and fabric items in my report .
Now my requirement is I need to have a slicer called search


Search = 'Fabric Items'[Item name]& " - " & 'Workspace'[workspacesname]

But Power BI not allowing me to create a column in this semantic model

Any alternate for this ? like by using measure for this or something

  • Hi viswaaa,

    You're facing this common limitation because when you connect to a semantic model (Power BI dataset live connection / direct semantic model), you cannot create calculated columns. Only measures are allowed.

    But don’t worry - you can still achieve your “Search” slicer requirement using a calculated table (allowed in semantic model reports) OR a measure-based field parameter trick, depending on your need.

     

    Firstly understand that Power BI disallows calculated columns when connected to a semantic model because the model is read-only in live connection mode. This is expected behaviour.

     

    Solution is to Create a Calculated Table for Search.

    You can create a combined Search Table using SUMMARIZE or CROSSJOIN.

    Create a Search Table: Go to Modeling >> New Table and enter:

    --------DAX--------

    Search Table =
    SELECTCOLUMNS (
    CROSSJOIN ( 'Fabric Items', 'Workspace' ),
    "Search", 'Fabric Items'[Item name] & " - " & 'Workspace'[workspacesname],
    "ItemName", 'Fabric Items'[Item name],
    "WorkspaceName", 'Workspace'[workspacesname]
    )
    --------DAX--------

    Now, Use Search Table[Search] as a slicer and 

    Create relationships if needed using ItemName or WorkspaceName.

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!

    Appreciate your Kudos!!

    Jaywant Thorat | MCT | Data Analytics Coach
    Linkedin: https://www.linkedin.com/in/jaywantthorat/


    Join #MissionPowerBIBharat = https://shorturl.at/5ViW9

    #MissionPowerBIBharat
    LIVE with Jaywant Thorat from 15 Dec 2025
    8 Days | 8 Sessions | 1 hr daily | 100% Free

9 Replies

  • viswaaa , is the direct lake/Live semantic model, then you need to create it at the source. If this direct query or Import mode, you should be able to create a new column. 
    You can create a measure but that can not be used in Group by 

    Search = CountX('Fabric Items', 'Fabric Items'[Item name]& " - " & related('Workspace'[workspacesname])) 

     

    Assuming Workspace is dimension 

    • viswaaa's avatar
      viswaaa
      Icon for Helper IV rankHelper IV

      Hi amitchandak ,

       

      But I cannot use this measure in my slicer right ?

      My requirement is to create this logic and use it as slicer

      • KarinSzilagyi's avatar
        KarinSzilagyi
        Icon for Super User rankSuper User

        Unfortunately, Measures can't be used in Slicers. You need to add it as a column (either calculated column or directly via your source)

  • Hi viswaaa , you can try these steps to solve your query 

    Step 1: Create a new table inside your report (allowed even on semantic model)

    Go to Modeling → New Table and create:

    DAX 
    Search Table =
    DISTINCT (
        SELECTCOLUMNS(
            'Fabric Items',
            "SearchText", 'Fabric Items'[Item Name] & " - " & RELATED('Workspaces'[WorkspaceName])
        )
    )


    If RELATED does not work because of inactive relationships, use this:

    DAX
    Search Table =
    DISTINCT (
        NATURALLEFTOUTERJOIN (
            SELECTCOLUMNS('Fabric Items',
                "ItemName", 'Fabric Items'[Item Name],
                "WorkspaceID", 'Fabric Items'[Workspace ID]
            ),
            SELECTCOLUMNS('Workspaces',
                "WorkspaceID", 'Workspaces'[WorkspacesID],
                "WorkspaceName", 'Workspaces'[WorkspaceName]
            )
        )
    )


    Then create:

    DAX
    Search Table = 
    ADDCOLUMNS(
        Search Table,
        "SearchText", [ItemName] & " - " & [WorkspaceName]
    )


    Step 2: Use SearchText as SLICER

    Step 3: Filter your visuals using Relationships or TREATAS
    If the search table has no relationship, use:

    Search Filter =
    TREATAS(
        VALUES('Search Table'[ItemName]),
        'Fabric Items'[Item Name]
    )

    Add this measure to the visual-level filter (set to not blank).

    Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
    💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
    🚀Let’s keep building smarter, data-driven solutions together!🚀[Explore More]

  • I'm guessing you're trying to get the data from the Fabric Capacity Metrics semantic model?

     

    At our company we extracted the data that we're using from the semantic model using a notebook and Python. It does require some work, but then you can build your own report with selected data from the Metrics report.

    I don't have a full documented step-by-step guide on how we dit this, but I did found this blog detailling how you can set this up:

    How to extract data from the Fabric Metrics App – Part 2 – PBI Guy

    Extra information


    https://learn.microsoft.com/en-us/fabric/data-science/read-write-power-bi-python

     

  • Hi viswaaa,

    You're facing this common limitation because when you connect to a semantic model (Power BI dataset live connection / direct semantic model), you cannot create calculated columns. Only measures are allowed.

    But don’t worry - you can still achieve your “Search” slicer requirement using a calculated table (allowed in semantic model reports) OR a measure-based field parameter trick, depending on your need.

     

    Firstly understand that Power BI disallows calculated columns when connected to a semantic model because the model is read-only in live connection mode. This is expected behaviour.

     

    Solution is to Create a Calculated Table for Search.

    You can create a combined Search Table using SUMMARIZE or CROSSJOIN.

    Create a Search Table: Go to Modeling >> New Table and enter:

    --------DAX--------

    Search Table =
    SELECTCOLUMNS (
    CROSSJOIN ( 'Fabric Items', 'Workspace' ),
    "Search", 'Fabric Items'[Item name] & " - " & 'Workspace'[workspacesname],
    "ItemName", 'Fabric Items'[Item name],
    "WorkspaceName", 'Workspace'[workspacesname]
    )
    --------DAX--------

    Now, Use Search Table[Search] as a slicer and 

    Create relationships if needed using ItemName or WorkspaceName.

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!

    Appreciate your Kudos!!

    Jaywant Thorat | MCT | Data Analytics Coach
    Linkedin: https://www.linkedin.com/in/jaywantthorat/


    Join #MissionPowerBIBharat = https://shorturl.at/5ViW9

    #MissionPowerBIBharat
    LIVE with Jaywant Thorat from 15 Dec 2025
    8 Days | 8 Sessions | 1 hr daily | 100% Free

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi viswaaa ,

    Thanks for reaching out to the Fabric Community.
    Since your report is connected to a read only semantic model, Power BI won't allow creating calculated columns or tables directly in the report. Because of this, a combined field like Item Name Workspace Name cannot be created at the report level. This is expected behaviour, as any structural changes have to be done in the main dataset itself.

    For now, the simplest and most practical method is to use two slicers, one for Item Name and one for Workspace Name and turn on the search option in both. This will let users quickly filter and narrow down results on both fields. If there’s a relationship between Workspaces and Items in your model, you can also use a hierarchy slicer so users can drill down from Workspace to Item in a single slicer. For a long term and proper solution that gives you one combined searchable field, the dataset owner or admin will need to create a concatenated column in the source model and republish the dataset. Once that is done, the combined Search field will appear directly in the report and can be used in a slicer hopefully.

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi viswaaa ,

      I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you