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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

COUNTROWS is offered only in measures and not in columns in case of by Desktop version (for the purpose of report Server) - in yhr compatibility link you've sent it's shown under "measures and query only".

 

In the formula, the filter does not recognize the DimentionUser value as related, because of the one to many relationship.

JirkaZ's avatar
JirkaZ
Icon for Solution Specialist rankSolution Specialist
6 years ago

Anonymous I was blind the whole time :smileyhappy:

I guess we can assume referential integrity. So the formula for the unused Dims could be:

 

UnusedDims = CALCULATE(COUNTROWS(DimDimension) - COUNTROWS(VALUES(DimDimensionUsers[DimID])))