Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 "o...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

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

     

     

    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.

  • Anonymous's avatar
    Anonymous
    2 years ago

    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 🤝

     

     

     

     

Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
2 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.