Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Choose related field based on selection

Based on a selection I need to get a value for a field that is related to this selection. For instance in the table below the user selects EQUIPMENT ID 3 from a slicer (in yellow below) which happens to be a Model A.  I am looking for some help for a DAX expression that would relate the selection of 3 to giving me a count of all Models that are the same as type 3 in this example:

Need Help

5 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    Hi Anonymous,

     

    Assuming that a Equipment can only have a model associated with it based on your data so you need to create this two measures:

     

    Model Select =
IF (
    DISTINCTCOUNT ( Equipments[Equipment ID] ) > 1;
    BLANK ();
    MAX ( Equipments[Model] )
)


Count Model =
CALCULATE (
    COUNT ( Equipments[Model] );
    FILTER (
        ALL ( Equipments[Model]; Equipments[Equipment ID] );
        Equipments[Model] = MAX ( Equipments[Model] )
    )
)

    The first measure returns the model name, if you have more than one value it will return blanks , the second counts the number of equipments similar to the Equipment ID associated.

     

     

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks for the response! 

       

      Couple questions:

      1.  How do I get "Model Select", a measure,  in a Slicer per your example? Measures can't be put in slicers.

      2.  I apologize but "Model" is in a different table so the ALL function is failing. Is there a way to account for Equipment ID and Model being in different tables?

       

       

       

       

       

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        hi,@Zagzebski 

             After my research, you can do these follow my steps as below:

        Step1:

        "Model" is in a different table, So create the relationship between them

         

        Step2:

        use these two formulas

        new Count Model = 
CALCULATE (
    COUNT ( Equipments[EQUIPMENT ID] ),ALL(Equipments[EQUIPMENT ID]),
    FILTER (
        ALL ( Table1[Model],Table1[EQUIPMENT ID] ),
        Table1[Model] = MAX ( Table1[Model] )
    )
)

        Result:

        when select "EQUIOMENT ID" is 3:

         

         And you only need to drag field model into slicer

        when select "model" is A:

        here is demo, please try it.

        https://www.dropbox.com/s/mxbk8l5u4njm3w2/Choose%20related%20field%20based%20on%20selection.pbix?dl=0

         

        Best Regards,

        Lin