none
Getting a single value out of a table in DAX RRS feed

  • Question

  • Hi,

    I am trying to write a DAX formula for a measure that would return me a single value from a column of the selected table, as opposed to an aggregation of this column.

    I have tried TOPN, but this returns a table, not a value that I can use in a measure.

    I cannot use LOOKUPVALUE because I need to be using the current filter context, I don't know which dimensions people will be filtering or not at run time.

    The test data is:



    The result I am getting if I use the standard CALCULATE( SUM (xxx), FILTER(xxx)) is as per this screenshot, where I have also indicated in green and red what I need to obtain:

    I would need a measure that does the following for Category 1:

    Do not return an aggregation of the Subcategory values,
    but instead the value that corresponds to the Category 1 row in the source table, i.e. 110
    the value needs to be filtered by the current context, and by Period being equal to "Period 1"

    Alternatively, if there was FIRST() aggregation, sorted by tree level, that would do the trick.


    I have looked at the solution at https://www.daxpatterns.com/parent-child-hierarchies/ but it uses an aggregation (SUM), which I need to avoid. Also our tables are big and the performance is problematic. I am hoping that going fetching the value will be faster than determining relative tree depth and aggregating.



    Monday, June 3, 2019 11:30 AM

Answers

  • hi,

    you can use this measure:

    Measure = 
    VAR
    sumofblanksub = CALCULATE([Sum of Value], FILTER(ALL(Table1), Table1[Subcategory] = BLANK()))
    VAR
    sumofnotblank = CALCULATE([Sum of Value], FILTER(Table1, Table1[Subcategory] <> BLANK()))
    RETURN
    IF(SELECTEDVALUE(Table1[Subcategory]) = BLANK(), sumofblanksub, sumofnotblank)

    and in the Visual Filter Panel, filter out the Subcategory <> blank

    • Marked as answer by jjhkzrg Tuesday, June 18, 2019 5:55 PM
    Sunday, June 16, 2019 1:46 AM