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 "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?

 

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 🤝

     

     

     

     

6 Replies

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

     

     

     

     

    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
      Not applicable
      2 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    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
    Not applicable
    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 🤝