Choose related field based on selection

[Anonymous]

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:

[MFelix]

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]

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?

[Ashish_Mathur]

Hi,

If model is in a different table then what is the lookup value/common column in both datasets.  Share both datasets here.

[v-lili6-msft]

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

Best Regards,

Lin

[MFelix]

Hi @v-lili6-msft,

Redo the measures I made to:

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



Count Model = 
CALCULATE (
    COUNT ( Table1[Model] );
    FILTER (
        ALL ( Table1[Model]; Table1[EQUIPMENT ID] );
        Table1[Model] = MAX ( Table1[Model] )
    )
)

See attach your PBIX file with the changes.

Be carefull because of the both side filtering between tables if you are using this tables in other calculations this can change your outcome.

Regards.

MFelix