Forum Discussion
Look up data based on multiple conditions
Hi.
Thank you for your advise.First I am reaching out to the once who manage the Power BI semantic model, and asking them to append the tables.
Hopefully they will do it, as it will be very time consuming and maintenance heavy to make measures for each grocery.
But thank you a lot.
Hi cmleo ,
In DirectQuery mode, calculated tables are not supported because they require in-memory processing, which isn't compatible with the real-time querying approach of DirectQuery. Instead of loading the full data model into memory, DirectQuery sends queries directly to the underlying data source only when required by visuals.
Alternatively, Use DAX Measures for dynamic calculations, consider using measures instead of calculated tables. Measures compute results based on the current report context without storing additional data. Transform Data in Power Query if you need to filter or reshape data from remote tables, use Power Query to perform these transformations before loading the data into your report.
Create Views in the Source System, if complex transformations are needed, consider building a view in the underlying data source to encapsulate the logic. You can then connect Power BI directly to this view.
Make sure you have the necessary permissions to access the shared semantic model and any underlying data sources. Check Data Source Settings: In the Power BI Service, ensure your data source settings and credentials are set up correctly for the shared semantic model.
Hope this helps,
Chaithra E.
Hi
I really appreciate your input.
My direct query tables comes from other Power BI semantic models. They are therefore, not visible in power wuery, and I cannot have calculated tabels. So I am limited to DAX measures, which I untill now hasn't had any success with.
So if you have good ideas to how I can convert my calculated column into a measure, I will be thrilled. 🙂
hello cmleo
as others mentioned about direct query, so i would do something like below.
create a new table contained of warehouse. This table only works as slicer.
create as many measures as your goods with this following DAX.
Apples =
IF(
SELECTEDVALUE('Warehouse'[Column1])="Warehouse 1",
MAXX(
FILTER(
'Table 3',
'Table 3'[Country]=SELECTEDVALUE('Table 1'[Country])&&
'Table 3'[Grocery]="Apples"
),
'Table 3'[Status]
),
IF(
SELECTEDVALUE('Warehouse'[Column1])="Warehouse 2",
MAXX(
FILTER(
'Table 4',
'Table 4'[Country]=SELECTEDVALUE('Table 1'[Country])&&
'Table 4'[Grocery]="Apples"
),
'Table 4'[Status]
),
IF(
SELECTEDVALUE('Warehouse'[Column1])="Warehouse 3",
MAXX(
FILTER(
'Table 5',
'Table 5'[Country]=SELECTEDVALUE('Table 1'[Country])&&
'Table 5'[Grocery]="Apples"
),
'Table 5'[Status]
)
)))Just change value 'Table 3'[Grocery], 'Table 4'[Grocery], and 'Table 5'[Grocery] for the other goods.
put all the measures into table visual
A little bit of works but as long as the result is achived.
the other thing, as you mentioned, you can not see the table value in direct query.
if you want to create a calculated table from a direct query table, i think you can summarize the direct query table.
You can see the value of direct query table in summarize table from direct query table. Last time i succeed by doing this.
Summarize all column in direct query table so you can prevent any value is missing because getting summarized.
Hope this will help.
Thank you.
Hi.
Thank you for your advise.First I am reaching out to the once who manage the Power BI semantic model, and asking them to append the tables.
Hopefully they will do it, as it will be very time consuming and maintenance heavy to make measures for each grocery.
But thank you a lot.
