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

[Anonymous]

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?

[v-lid-msft · Accepted solution]

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]

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.

[Anonymous]

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

[JirkaZ]

@Anonymous You can add a column to the DimDimension using COUNTROWS(RELATEDTABLE(DimDimensionUser)). Then just count the rows that have 0

[Anonymous]

@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-2016

As a single measure you could maybe calculate it like:
Count without child := CALCULATE(Countrows(DimDimension) - CALCULATE(Countrows(DimDimension), FILTER(DimDimension(Id) = DimDimensionUsers(ParentId))))

[Anonymous]

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 

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

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

[Anonymous]

@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.