none
How to find most frequent (mode) value using DAX (on attribute/value table) RRS feed

  • Question

  • Hi would like to figure out how to calculate the mode aggregate, in special for the special situation below:

    itemID;parameterID;M
    1,100,42
    1,200,5
    1,300,2
    1,400,11

    2,100,44
    2,200,0
    2,400,11

    3,100,1
    3,200,5
    3,500,2
    3,400,11

    4,100,42
    4,200,5
    4,400,11

    ("Mode" means most frequent value - mode is a term from statistics in this context)

    What is the mode of the value (M) for parameterID=100 where M=5 for parameterID=200 and M=11 for parameterID=400?
    (parameter for same item, parameterID=300 not relevant)

    The answer is of course 42 ;^)
    For itemID=2: M=0 for parameterID=200 does not fit the requirment (M=11)
    For itemID=3: the filter conditions are met

    Remark that parameters are grouped by item, e.g. we are dealing
    here with at attribute/value table. For low values of parameters the datamodel should have been pivoted,
    but that is not practical for large amount of parameters (not known at design time).

    links etc are welcome :-)

     

     

     


    B. D. Jensen

    Monday, August 5, 2013 3:03 PM

All replies