topic Re: how to use Joins and Subquries in Power Bi in DAX Commands and Tips

how to use Joins and Subquries in Power Bi

kousar99 — Mon, 25 Apr 2022 08:47:32 GMT

Hi,

Since I am using SQL queries and whatever the output is coming from queries same thing showing in form of charts in power bi.

Since I'm new to power bi and Dax, I need help and suggestions for how to use subqueries used in SQL ( As per my thinking filtering is done) and joins in power bi and Dax.
Looking for your kind response.

Thanks & Regards,

Heena Kousar

Re: how to use Joins and Subquries in Power Bi

amitchandak — Mon, 25 Apr 2022 09:07:46 GMT

kousar99 , Please share a problem, while we can create a table and pass it as a filter. But in DAX we do it DAX way, maybe a better solution is available

Check

Dax vs SQL -https://www.youtube.com/watch?v=WlvQ_SGy4iA&list=PLPaNVDMhUXGZNyKU0PgG2g3P0c6CPjMnj

example

measure =
var _tab = summarize(filter(Table,{empresa_id]=4),[tenant_id])
return
calculate(sum(Table[Value]), filter(Table,Table[tenant_id] in _tab))

measure =
var _tab = summarize(filter(A, [date]= date(2020,11,05) && [State] ="Step2" && [Status] = "Status" ), A[FileName])
return
calculate(countrows(A) , filter(A , [FileName] in _tab && [State] ="Step2" && [Status] = "Status" ))

measure =
var _tab = summarize(filter(all(users), users[usermail] in values(users[usermail]) || users[businessrecipient] in values(users[usermail])),users[businessunit])
return
calculate(countrows(masterlist), filter(masterlist, masterlist[businessunit] in _tab))

refer

https://docs.microsoft.com/en-us/dax/treatas-function

Re: how to use Joins and Subquries in Power Bi

Anonymous — Tue, 03 May 2022 05:52:58 GMT

Hi kousar99,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng