Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count dimentions with missing values (Desktop May2019 for report server)

I have two tables with one to many relationship and crossfilter direction set to "both".   DimDimention (has DimentionID-s) 1 - * DimDimentionUser (has DimentionID's and UserID's)   I need to cal...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,


    How about the result after you follow the suggestions in other's post? if your ploblem does not resolved, You could also try the following formula.

     

    Count =
DISTINCTCOUNT ( DimDimentionID[DimentionId] )
    - CALCULATE (
        DISTINCTCOUNT ( DimDimentionID[DimentionId] ),
        FILTER ( ALL ( 'DimDimentionUser' ), [DimentionId] & "" <> "" )
    )

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,


How about the result after you follow the suggestions in other's post? if your ploblem does not resolved, You could also try the following formula.

 

Count =
DISTINCTCOUNT ( DimDimentionID[DimentionId] )
    - CALCULATE (
        DISTINCTCOUNT ( DimDimentionID[DimentionId] ),
        FILTER ( ALL ( 'DimDimentionUser' ), [DimentionId] & "" <> "" )
    )

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    The measure gives  the right calculation, so is correct.

    Unfortunately, it does not help to find the DimId-s without a user (i.e. does not show the right values on rows), as I need to also get a list of them.

    As no DAX solutions was found I went ahead of adding a "Count User" column to the original SQL data via SQL. 

    • v-lid-msft's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous ,

       

      The formula is purpose to find total count of the dimentions without user, If you need to get the ID list, we can try the following solution:

       

      Put the DimID Column  of DimDimension Table into a table visual, Create a measure and put into the visual Filter.

       

      HasUser = DISTINCTCOUNT('DimDimensionUsers')

       

      Then we can set the HasUser greater than zero to archieve your requirement.

       

      Best regards,

      Community Support Team _ Dong Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        As they are crossfiltered the Users with no  Domentions are not shown, i.e. the measures gives 1 on all rows.