Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX equivalent sql : select column from table1 where column in (select distinct column from table2)

Hello,
I have a model with 2 fact table and shared dimension. User apply filters on on fact1 to obtain a list of "CodeClient".
I need to filter fact2 based on that list of "CodeClient" (i.e : Calculate the sum of measures in fact2 where "CodeClient" in distinct value selected in step1 ) see attached pbix : https://drive.google.com/open?id=1UgusmAEQ5brbTwdLaoGyopnZlhcSJyKz

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous ,

    Visual level filter only works on current visual, you can't get filter items from other visual.
    I'd like to suggest you break relationships between two fact tables and use slicer to instead visual level filter.
    Then you can write a measure filter to compare with current row contents and filtered records and return tag.(apply this tag filter on fact2 table 'visual level filter' to filter correspond records)

    Measure =
VAR currClient =
    SELECTEDVALUE ( Fact2[CodeClient] )
VAR clientList =
    CALCULATE ( VALUES ( Fact1[CodeClient] ), ALLSELECTED ( Fact1 ) )
RETURN
    IF ( currClient IN clientList, "Y", "N" )

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous 

      Thanks for your reply, this can be a good solution, but in fact1 table I have a measure used as a filter, and that measure combine fields with OR condition and use a slicer Something like that : 

      TagMeasure = If ( (max(fact1[val1]) >= Selectedvalue(Table[Slicer])
                       && max(fact1[val2]) <= max(fact1[val3])
                  ) || 
                  max(fact1[val2]) <= 0 
                ; 1 ; 0 )

        This measure serve also as a filter for fact1 table and cant't be used as a page level filter so ignored by the ALLSELECTED function in your code.