Convert SQL Query to Dax measure

[sirpentagon]

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. 

[JarroVGIT · Accepted solution]

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

[v-lid-msft · Accepted solution]

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,

[JarroVGIT · Accepted solution]

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