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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
PowerBINewbie_
Frequent Visitor

Looking for the equivalent of a SQL-statement for Power BI/Power Query

I am looking for the possibility to display all information in a table for the value of column HistID by giving an input of another column (PropertyValue), similar to the SQL statement below. Both columns are part of the same table (EventHistory).

 

select * 
from EventHistory
where HistID in (
               select HistID 
               from EventHistory
               where PropertyValue = '10048')

 

The value of 10048 should be flexible and be able to be put in with a textfilter in the PowerBI report by the user.

 

1 ACCEPTED SOLUTION
rubayatyasmin
Super User
Super User

Hi, @PowerBINewbie_ 

Here is a revised version. See if that helps. 

 

You want to avoid using aggregation functions like COUNTROWS and display all the data points for the selected HistID and PropertyValue combination.

You can achieve this in Power BI by following these steps:

  1. Create a new dimension table that contains unique combinations of HistID and PropertyValue. Let's call this table "DimHistPropertyValue."

  2. In the "EventHistory" table, create a relationship between "HistID" in the "EventHistory" table and "HistID" in the "DimHistPropertyValue" table.

  3. Create a slicer using "PropertyValue" from the "DimHistPropertyValue" table to allow users to select the desired PropertyValue.

  4. Create a table visual in Power BI that includes the columns "HistID," "PropertyName," and "PropertyValue" from the "EventHistory" table.

  5. Use the slicer created in step 3 to filter the table visual based on the selected PropertyValue.

  6. When a user selects a PropertyValue from the slicer, the table visual will automatically show all the data for the selected HistID and PropertyValue combination, including the other data points such as Speed and Temperature.

By creating a separate dimension table for HistID and PropertyValue and establishing a relationship with the EventHistory table, you can easily filter the data without using any aggregation function, allowing you to display all the relevant information for the selected HistID and PropertyValue combination. 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

7 REPLIES 7
foodd
Super User
Super User

@rubayatyasmin , thank you for your willingness to describe a solution step-by-step, and in plain language for @PowerBINewbie_ .

rubayatyasmin
Super User
Super User

Hi, @PowerBINewbie_ 

Here is a revised version. See if that helps. 

 

You want to avoid using aggregation functions like COUNTROWS and display all the data points for the selected HistID and PropertyValue combination.

You can achieve this in Power BI by following these steps:

  1. Create a new dimension table that contains unique combinations of HistID and PropertyValue. Let's call this table "DimHistPropertyValue."

  2. In the "EventHistory" table, create a relationship between "HistID" in the "EventHistory" table and "HistID" in the "DimHistPropertyValue" table.

  3. Create a slicer using "PropertyValue" from the "DimHistPropertyValue" table to allow users to select the desired PropertyValue.

  4. Create a table visual in Power BI that includes the columns "HistID," "PropertyName," and "PropertyValue" from the "EventHistory" table.

  5. Use the slicer created in step 3 to filter the table visual based on the selected PropertyValue.

  6. When a user selects a PropertyValue from the slicer, the table visual will automatically show all the data for the selected HistID and PropertyValue combination, including the other data points such as Speed and Temperature.

By creating a separate dimension table for HistID and PropertyValue and establishing a relationship with the EventHistory table, you can easily filter the data without using any aggregation function, allowing you to display all the relevant information for the selected HistID and PropertyValue combination. 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


BA_Pete
Super User
Super User

Hi @PowerBINewbie_ ,

 

It sounds like you should be able to just create a dimension table holding [HistID] and [PropertyValue], then relate it to EventHistory on dim[HistID] = EventHistory[HistID]. You would then use dim[PropertyValue] in a searchable slicer for users to select from.

 

How many different [HistID]s can each [PropertyValue] have, and vice versa?

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi @BA_Pete, thanks a lot for your reply!

There can be more than one different values in both directions.

 

PowerBINewbie_
Frequent Visitor

Hi @rubayatyasmin,

thanks for your answer!

My only problem remaining is that I don't want to use an aggregation such as COUNTROWS on the filtered data, I want to display all data from the remaining columns (the table was pivoted before, therefore also other datapoints from the column 'PropertyValues' should be shown, for example:

 

HistIDPropertyNamePropertyValue
1BatchNr.10048
1Speed30
1Temperature100

 

My goal is to look for the PropertyValue  10048 and get the other data for Speed (30) and Temperature (100) in return.

I mentioned in the solution you need to slicer visual in PBI. use the slicer to filter the table based on 'PropertyValue'. When you select '10048' in the slicer, the table will update to only display the rows where 'PropertyValue' is '10048'.

 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


rubayatyasmin
Super User
Super User

Hi, @PowerBINewbie_ 

 

By looking at your name, I assume you are new to PBI and might need step by step guide. Here is the steps you need to follow. 

 

1. First, import your data into Power BI.

2. Create a new measure. (you will have to use CALCULATE, FILTER, CALCULATETABLE and SELECTEDVALUE -(this one is for slicer interaction)

 

demo code:

 

HistID_Filtered =
CALCULATE(
COUNTROWS('EventHistory'),
FILTER(
'EventHistory',
'EventHistory'[HistID] IN
CALCULATETABLE(
VALUES('EventHistory'[HistID]),
'EventHistory'[PropertyValue] = SELECTEDVALUE('EventHistory'[PropertyValue])
)
)
)

 

Please note: replace the 'COUNTROWS' with whatever calculation you want to do on your filtered data.

 

3. create a slicer using PropertyValue field

4. Use the newly created measure in the visualization. 

 

to better understand the DAX functions see these documents. 

 

Calculate - CALCULATE function (DAX) - DAX | Microsoft Learn

CALCULATETABLE function (DAX) - DAX | Microsoft Learn

SELECTEDVALUE function - DAX | Microsoft Learn

VALUES function (DAX) - DAX | Microsoft Learn

Aggregation functions (DAX) - DAX | Microsoft Learn

 

hope this helps. 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors