Forum Discussion
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.
- 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 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,
2 Replies
- 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 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,