Forum Discussion

arif_tsrm's avatar
arif_tsrm
Helper I
2 years ago
Solved

Dynamic N-Value (TOPN) for Least Popular Day Calculation

 

I am trying to calculate Least popular day using the following DAX, but I am getting the fixed result ( Friday ) although there is no value ( blank) in friday, I think the result is fixed Friday is  only for that  I am using  fixed N-Value 5, is there any way we can set the N-Value dynamically?

LeastPopularDayOfWk=

FIRSTNONBLANK (
TOPN (
5,
CALCULATETABLE (
VALUES ( DimDayName[DayName] ),
FILTER ( DimWP, DimWP[WP] <> "Remote" )
),
[%Of_Attendance_By_DayForM/LPD]
),
1
)

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi arif_tsrm ,

    I do some changes on my DAX codes.

    Measure 2 = 
VAR _A =
    MINX (
        FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[Attendance] ) ) ),
        'Table'[Attendance]
    )
RETURN
    LOOKUPVALUE ( 'Table'[DayShort], 'Table'[Attendance], _A )

    Then you can see that it will return you the smallest value other than blank.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi arif_tsrm ,

    I create a table as you mentioned.

    Then I create a measure and here is the DAX code.

    Measure =
VAR _Lastest =
    CALCULATE ( MAX ( 'Table'[Number] ), ISBLANK ( 'Table'[Attendance] ) )
RETURN
    CALCULATE (
        MAXX ( FILTER ( 'Table', 'Table'[Number] = _Lastest ), 'Table'[DayShort] ),
        ISBLANK ( 'Table'[Attendance] )
    )

    Now you can use dynamic N-Value (TOPN) for least popular day.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • arif_tsrm's avatar
      arif_tsrm
      Helper I
      2 years ago

      Thanks for your reply , you have created the table is data table but my mentioned table is a visualization , I want to calculate the least popular day based on value of the measure  [%Of_Attendance_By_DayForM/LPD],   if in any days the  [%Of_Attendance_By_DayForM/LPD] is blank then those days we not consider . for this data scenerio the least popular day will be Tuesday and Wednesday ( because the lowest value is 50%) instead of Friday.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi arif_tsrm ,

        I do some changes on my DAX codes.

        Measure 2 = 
VAR _A =
    MINX (
        FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[Attendance] ) ) ),
        'Table'[Attendance]
    )
RETURN
    LOOKUPVALUE ( 'Table'[DayShort], 'Table'[Attendance], _A )

        Then you can see that it will return you the smallest value other than blank.

         

         

         

        Best Regards

        Yilong Zhou

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.