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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

How to filter a table visual based on column value exist in both direct query tables or not?

I have made a table visual based on two (parameter-driven) Direct query tables that both contains a (unique) companyID column.

In the data model the relation between the two tables are defined as "one to one" (1:1) with filtering direction "both ways".

So far I have unsuccessfully tried to define a measure in one of the tables that I can use in a slicer to filter the table visual if the column values exist in both direct query tables or not.

inBothTables =
var _T1 = SELECTEDVALUE('Table1'[companyID])
var _T2 = SELECTEDVALUE('Table2'[companyID])
return
IF(_T1 = _T2, "Yes", "No")

 

I also tried this code:

inBothTables = IF ( COUNTROWS ( FILTER ( ALL('Table2'), 'Table2'[companyID] = 'Table1'[companyID] ) ) > 0, "Yes", "No" )

 

How do I code a measure (or new column?) in order to make this work? Or do I simply have to provide this filter column in one of the table queries?

 

2 ACCEPTED SOLUTIONS
v-tianyich-msft
Community Support
Community Support

Hi @Anonymous ,

 

It is possible, I made simple samples and you can check the results below:

vtianyichmsft_0-1703238744472.png

vtianyichmsft_1-1703238752719.png

 

 

Column = var _t = VALUES('Table (2)'[companyID])
RETURN IF('Table'[companyID] in _t,"Yes","No")

 

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

I have spent some hours to work out how to create a slicer in my Direct Query project. That also includes testing quite a few script suggestions from ChatGPT. Unfortunately none of them worked out as I repeatedly got returned error messages from PowerQuery telling me that the given DAX function was not allowed in the context of Direct Query tables.

 

The solution I finally worked out was to make another (parameter-driven) Direct Query table "BothProd_CompanyIDs" with the joined companyIDs from the SQL Server source table for Table1 and Table2. I also splitted the CompanyID column into a "Prod1_CompanyID" column.

 

Then I created a new column in the "BothProd_CompanyIDs" table and was finally able to assign this column to a slicer.

 

Column = var _t = VALUES(BothProd_CompanyIDs'[Prod1_companyID])
RETURN IF('BothProd_CompanyIDs'[companyID] in _t,"Yes","No")

 

Maybe a cumbersome way to do it but it finally worked 🙂

 

Thanks a lot for the contributions on this topic 🤝

 

 

 

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

I have spent some hours to work out how to create a slicer in my Direct Query project. That also includes testing quite a few script suggestions from ChatGPT. Unfortunately none of them worked out as I repeatedly got returned error messages from PowerQuery telling me that the given DAX function was not allowed in the context of Direct Query tables.

 

The solution I finally worked out was to make another (parameter-driven) Direct Query table "BothProd_CompanyIDs" with the joined companyIDs from the SQL Server source table for Table1 and Table2. I also splitted the CompanyID column into a "Prod1_CompanyID" column.

 

Then I created a new column in the "BothProd_CompanyIDs" table and was finally able to assign this column to a slicer.

 

Column = var _t = VALUES(BothProd_CompanyIDs'[Prod1_companyID])
RETURN IF('BothProd_CompanyIDs'[companyID] in _t,"Yes","No")

 

Maybe a cumbersome way to do it but it finally worked 🙂

 

Thanks a lot for the contributions on this topic 🤝

 

 

 

 

v-tianyich-msft
Community Support
Community Support

Hi @Anonymous ,

 

It is possible, I made simple samples and you can check the results below:

vtianyichmsft_0-1703238744472.png

vtianyichmsft_1-1703238752719.png

 

 

Column = var _t = VALUES('Table (2)'[companyID])
RETURN IF('Table'[companyID] in _t,"Yes","No")

 

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

v-tianyich-msft
Community Support
Community Support

Hi @Anonymous ,

 

Measure can not be put into slicer. You just need to go through filter and change the condition of measure to is Yes.

vtianyichmsft_2-1703213104999.png

vtianyichmsft_3-1703213116948.png

 

 

 

 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

Anonymous
Not applicable

Thank you for this reply. Is it possible to compute a new "Yes/No" column and assign it to a slicer - instead of using a measure? Any suggestion on how to script this column?

Sahir_Maharaj
Super User
Super User

Hello @Anonymous,

 

Can you please try this:

ExistsInBothTables = 
VAR currentCompanyID = SELECTEDVALUE(Table1[companyID])
RETURN IF(
    CALCULATE(
        COUNTROWS(Table2),
        FILTER(Table2, Table2[companyID] = currentCompanyID)
    ) > 0, "Yes", "No"
)

Should you require any further assistance, please do not hesitate to reach out to me.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Anonymous
Not applicable

Thank you very much for your script design! It computes correctly but also generates lots of redundant rows when I insert the measure in the field input in the visual table. Any suggestion why this happends?
Unfortunately it does not seems to be possible to assign the measure to a slicer when I use Direct query tables.
I tried to create a new table with the "Yes"/"No" values and assign it to the slicer but it does not change the table visual. It is also not possible to make a relation between the measure and the corresponding field in the new table in the data model view.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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