Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
sirpentagon
Frequent Visitor

Convert SQL Query to Dax measure

select

    InstanceId, count(DISTINCT Date)
from
    Storage
where
    month(date) = 12
    and year(date) = 2019
    and tags like '%DemoManaged%'
group by

    InstanceId

 

 

Hello, I am very new to Power BI and need help to create a new measure. The Main table (Storage) has around 25-30 columns.

The table contains InstanceId which I need to display in a Power BI - Table visualization along with the count.

I am able to do it through SQL query, and I am not able to create a new Measure for the same.

 

Any help is highly appreciated. 

2 ACCEPTED SOLUTIONS
JarroVGIT
Resident Rockstar
Resident Rockstar

Hi,
There is no need to create measures, you are asking for core functionality of PowerBI.
1. Create a table visual by clicking it in the Visuals pane.
2. Add Instance ID column by dragging it into the new table visual
3. Add Date column to visual by dragging it into the visual.
4. In the Visual pane, click on the little arrow downwards of Date entry and make sure it is not set to Date Hierarchy but to Date value. Then Reopen that menu and click count distinct.
Open the Filter pane and drag columns you want to filter to the Visual Filter area and apply the required filters.

Let me know of this answers your question and if it does please accept it as the solution.
Kind regards




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

v-lid-msft
Community Support
Community Support

Hi @sirpentagon ,

 

We can use the following measure after put the id into the table visual to meet your requirement:

 

Count =
CALCULATE (
    DISTINCTCOUNT ( 'Storage'[Date] ),
    FILTER (
        'Storage',
        'Storage'[Date] >= DATE ( 2019, 12, 1 )
            && 'Storage'[Date] <= DATE ( 2019, 12, 31 )
            && CONTAINSSTRING ( 'Storage'[tags], "DemoManaged" )
    )
)


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lid-msft
Community Support
Community Support

Hi @sirpentagon ,

 

We can use the following measure after put the id into the table visual to meet your requirement:

 

Count =
CALCULATE (
    DISTINCTCOUNT ( 'Storage'[Date] ),
    FILTER (
        'Storage',
        'Storage'[Date] >= DATE ( 2019, 12, 1 )
            && 'Storage'[Date] <= DATE ( 2019, 12, 31 )
            && CONTAINSSTRING ( 'Storage'[tags], "DemoManaged" )
    )
)


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JarroVGIT
Resident Rockstar
Resident Rockstar

Hi,
There is no need to create measures, you are asking for core functionality of PowerBI.
1. Create a table visual by clicking it in the Visuals pane.
2. Add Instance ID column by dragging it into the new table visual
3. Add Date column to visual by dragging it into the visual.
4. In the Visual pane, click on the little arrow downwards of Date entry and make sure it is not set to Date Hierarchy but to Date value. Then Reopen that menu and click count distinct.
Open the Filter pane and drag columns you want to filter to the Visual Filter area and apply the required filters.

Let me know of this answers your question and if it does please accept it as the solution.
Kind regards




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.