Forum Discussion
How to use the same filter function in M Query but in DAX
Hello Everyone,
I have a rather complicated issue. I wrote a query that inner joins two tables together based on a date range. I then merge that whole query to another table that checks for a particular id in my previous table based on an id i provided in the "where" clause.
Query 1:
SELECT distinct e1.pc_id
FROM dbo.example e1
INNER JOIN dbo.example2 e2 ON e1.pc.id = e2.pc.id
where e1.ScanDate >= getdate()-14
Query 2:
LEFT OUTER JOIN dbo.example3 e3
where e3.pc.id like "='lab pc'
I used power bi to merge the query 2 using a left outer join to my Query 1. The reason for this is to check only for "lab pc" in my previous query and return null for every row that doesnt match that search. When i loaded it into my data model, i created a calculated column that takes the count of what was found and what wasnt. I then created a % calculations for both as well
This is works for perfectly. My issue is, i want to automate the process by listing all the types of pc id but when i select "Lab pc" from the list of all pc id's i want it to only show me just the % for that pc id type. Right now, if i leave it prefilterd using power query it works but when i remove the filter, bring it into my model and apply a page or report filter, it doesnt work ( which i expect).
My issue now is a way to create that same filter in DAX that will only show result for my filtered id when i select it from the lists of id's and make all other result value not added to what i select. Basically, when i click to select the right id from the list, it should make the id i select 1 and the rest 0 on my table result so that i can only see the effect of that selection on my data. Thank you.
1 Reply
- rocky09
Solution Sage
Hi fellaz4sho
the best way to get a quick solution is to provide some sample data and a screenshot of your problem and the results you are expecting. So, you will get many possible solutions. I hope, you understand. :)