Forum Discussion

Bratone's avatar
Bratone
Icon for Helper I rankHelper I
3 years ago

Remove blanks without changing the measure value

Hello,   I have the following situation. I want to calculate the % of leaves for a specific team and its members. The measure that I used is very basic:   %ofLeaves = [NrOfLeaves] / [AvailableRes...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Bratone ,

Please try below steps:

1. below is my test table

2. create two meaure with below dax formula

Measure =
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Manager] = SELECTEDVALUE ( 'Table'[Manager] )
    )
VAR sum_nr =
    SUMX ( tmp, [NrOfLeaves] )
VAR sum_ab =
    SUMX ( tmp, [Availables] )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Table'[Sub_Mg] ),
            DIVIDE (
                SELECTEDVALUE ( 'Table'[NrOfLeaves] ),
                SELECTEDVALUE ( 'Table'[Availables] )
            ),
        ISINSCOPE ( 'Table'[Manager] ), DIVIDE ( sum_nr, sum_ab ),
        DIVIDE (
            SUMX ( ALL ( 'Table' ), [NrOfLeaves] ),
            SUMX ( ALL ( 'Table' ), [Availables] )
        )
    )

Measure2 =
VAR tmp =
    CALCULATETABLE ( VALUES ( 'Table'[Sub_Mg] ), 'Table'[NrOfLeaves] = BLANK () )
VAR cur_submg =
    SELECTEDVALUE ( 'Table'[Sub_Mg] )
RETURN
    IF ( cur_submg IN tmp, 0, 1 )

3. add a matrix visual, then add fields and "Measure" to it, add "Measure 2" to the "Filters" pane

 

add measure to "Filter" pane is used to don't show the blank data.

Befor add:

After add:

 

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Bratone's avatar
    Bratone
    Icon for Helper I rankHelper I
    3 years ago

    Hello,


    Thank you for taking the time to reply, the problem is that in my case, NrOfLeaves and AvailableResources are actually measures, not columns in the same raw data tabel:

     

    NrOfLeaves = CALCULATE(COUNTA(Schedule_Manager[Leave Category]),Schedule_Manager[Leave Category]<>BLANK()) ==> Basically this counts the number of Leave Category reasons for every individual, to see how many leave days they had
     
    AvailableResources = (CALCULATE(COUNTA(Employeess[Login Key]),Employeess[Attrition]="No"))*[BusinessDays] ==> Basically this checks if the individual is an active employee and if yes his "score" for AvailableResource is multiplied with the number of BusinessDays in that week/month etc.
     
    So any idea on how can I adapt the formula to take into account the measures and not the columns ?
     
    Thanks again!
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Bratone ,

      Since I don't know what your actual environment is like, I couldn't give you an exact answer, but for measure you could also use similar operations to columns, and I think my example is a good reference for you. Please try to adjust it by yourself.

       

      Best regards,
      Community Support Team_ Binbin Yu
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.