Forum Discussion

Prakash1050's avatar
Prakash1050
Helper III
3 years ago
Solved

Need Dax Measure

Hi All,

           I have 3 tables "a", "b" and "c". The "a", "b" table have one common column and "b", "c" table have one common column. If in the filter pane i selected one value of common column in "a" table should be filter in "c" table. But I don't want to create a relationship between these tables only Measure can be done this scenario. Please help me.

Thanks in Advance.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Prakash1050,

    It seems like I shared the wrong formulas, you can take a look at the  following formulas that I fixed the wrong part and modify with the field name that you shared to get calculation result from Dax condition filters without really relationship mappings.

    formula =
VAR idList =
    CALCULATETABLE (
        VALUES ( TableB[Entity ID] ),
        FILTER ( ALLSELECTED ( TableB ), TableB[Key] IN VALUES ( TableA[Key] ) )
    )
RETURN
    CALCULATE (
        SUM ( TableC[Value] ),
        FILTER ( ALLSELECTED ( TableC ), TableC[Entity ID] IN idList )
    )

    Regards,

    Xiaoxin Sheng

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Prakash1050,

    It seems like you want to achieve two step filter effect without real relationship mappings, you can take a look at me following measure formula if it helps:

     

    formula =
VAR list =
    CALCULATE (
        SUM ( TableB[Column2] ),
        FILTER ( ALLSELECTED ( TableB ), TableB[Column1] IN VALUES ( TableA[Column1] ) )
    )
RETURN
    CALCULATE (
        SUM ( TableC[Value] ),
        FILTER ( ALLSELECTED ( TableC ), TableC[Column2] IN list )
    )

     

    Regards,

    Xiaoxin Sheng

  • Prakash1050's avatar
    Prakash1050
    Helper III
    3 years ago

    Hi Anonymous ,
      This measure is not correct i clearly mention in the below kindly help me.

       I need DAX Measure for not creating relationship and get the answer. 

            I have 3 Tables A, B, C.

            A table have BU, Composite and Key columns.

            B table have Key, Entity ID and Fam ID.          

            C table have Entity ID and Value.

            A table key column and B table key column are common column.

            B table Entity ID column and C table Entity ID column are common column.

            I need DAX for in the filter pane if A table key column selected the B table key column also filterd as well as B table key column direct to the B Table Entity ID Column also filtered and the same Enitity ID in C table column also filterd and get the Value Column answer.

           I hope this scenario you understand please help me to acheive this DAX.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Prakash1050,

      It seems like I shared the wrong formulas, you can take a look at the  following formulas that I fixed the wrong part and modify with the field name that you shared to get calculation result from Dax condition filters without really relationship mappings.

      formula =
VAR idList =
    CALCULATETABLE (
        VALUES ( TableB[Entity ID] ),
        FILTER ( ALLSELECTED ( TableB ), TableB[Key] IN VALUES ( TableA[Key] ) )
    )
RETURN
    CALCULATE (
        SUM ( TableC[Value] ),
        FILTER ( ALLSELECTED ( TableC ), TableC[Entity ID] IN idList )
    )

      Regards,

      Xiaoxin Sheng

      • Prakash1050's avatar
        Prakash1050
        Helper III
        3 years ago

        Hi Anonymous 
            Thank you so much this measure is working correctly.