Forum Discussion

Prakash1050's avatar
Prakash1050
Helper III
3 years ago
Solved

Need Dax Measure

Hi All,

        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.

 

Thanks in Advance.

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Prakash1050 ,

    Please update the formula of measure as below and check if it can work even though filter the value of field [BU] or [Composite] field in the table A. 

    Measure = 
VAR _selkeys =
    CALCULATETABLE (
        VALUES ( 'A'[Key] ),
        ALLSELECTED('A')
    )
VAR _entitylist =
    CALCULATETABLE (
        VALUES ( 'B'[Entity ID] ),
        FILTER ( 'B', 'B'[Key] IN _selkeys )
    )
RETURN
    CALCULATE ( SUM ( 'C'[Value] ), FILTER ( 'C', 'C'[Entity ID] IN _entitylist ) )

    Best Regards

4 Replies

  • Prakash1050's avatar
    Prakash1050
    Helper III
    3 years ago

    Hi Anonymous 
              This Measure is worked when i filtered A table Column Key. If filtered BU or Composite in Table A the value showing same value not filtered values

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Prakash1050 ,

      Please update the formula of measure as below and check if it can work even though filter the value of field [BU] or [Composite] field in the table A. 

      Measure = 
VAR _selkeys =
    CALCULATETABLE (
        VALUES ( 'A'[Key] ),
        ALLSELECTED('A')
    )
VAR _entitylist =
    CALCULATETABLE (
        VALUES ( 'B'[Entity ID] ),
        FILTER ( 'B', 'B'[Key] IN _selkeys )
    )
RETURN
    CALCULATE ( SUM ( 'C'[Value] ), FILTER ( 'C', 'C'[Entity ID] IN _entitylist ) )

      Best Regards

      • Prakash1050's avatar
        Prakash1050
        Helper III
        3 years ago

        Hi Anonymous 
        This Measure is Working Thank you so much

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Prakash1050 ,

    You can create a measure as below to get it, please find the details in the attachment.

    Measure = 
VAR _selkeys =
    ALLSELECTED ( 'A'[Key] )
VAR _entitylist =
    CALCULATETABLE (
        VALUES ( 'B'[Entity ID] ),
        FILTER ( 'B', 'B'[Key] IN _selkeys )
    )
RETURN
    CALCULATE ( SUM ( 'C'[Value] ), FILTER ( 'C', 'C'[Entity ID] IN _entitylist ) )

    Best Regards