Forum Discussion
Count dimentions with missing values (Desktop May2019 for report server)
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.
Solution SpecialistAnonymous You can add a column to the DimDimension using COUNTROWS(RELATEDTABLE(DimDimensionUser)). Then just count the rows that have 0
JirkaZ, unfortunately the calculated column does not offer me the "countrows" formula (i'm using DirectQuery)
- JirkaZ
Anonymous COUNTROWS should be available for use in measures even in DirectQuery mode
https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016As a single measure you could maybe calculate it like:
Count without child := CALCULATE(Countrows(DimDimension) - CALCULATE(Countrows(DimDimension), FILTER(DimDimension(Id) = DimDimensionUsers(ParentId))))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
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])))