Forum Discussion
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 calculate the number of dimentions that do not have a user, i.e. count DimDimention ID's when that DimDimentionID is missing from DimDimentionUser.
I wanted to use the following formulas, however the Desktop version is May2019 for the Reporting Server and does not have SELECTEDCOLUMNS, EXCEPT, ISEMPTY formulas.
The following formula does not work either
calculate(
distinccount(DimDimentionId);
filter(DimDimentionUser, isblank(DimDimentionUser[DimentionId])
)
Any ideas how to solve this with a calculated measure?
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.
11 Replies
- JirkaZ
Solution Specialist
Anonymous You can add a column to the DimDimension using COUNTROWS(RELATEDTABLE(DimDimensionUser)). Then just count the rows that have 0
JirkaZ,Will this work as a measure?
- JirkaZ
The first part has to be a column in the DimDimension. The countrows part can be a Measure.
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))))
v-lid-msftCommunity Support
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.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
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.
