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

[Anonymous]

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.

[foodd]

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

[rubayatyasmin · Accepted solution]

Hi, @Anonymous 

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. 

[BA_Pete]

Hi @Anonymous ,

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

[Anonymous]

Hi @BA_Pete, thanks a lot for your reply!

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

[Anonymous]

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:

HistID	PropertyName	PropertyValue
1	BatchNr.	10048
1	Speed	30
1	Temperature	100

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

[rubayatyasmin]

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]

Hi, @Anonymous 

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.