Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Hi @PBIacc721 ,
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.
Hi @PBIacc721 ,
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.
Hi @PBIacc721 ,
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.
As they are crossfiltered the Users with no Domentions are not shown, i.e. the measures gives 1 on all rows.
@PBIacc721 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)
@PBIacc721 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-direc...
As 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.
@PBIacc721 I was blind the whole time
I guess we can assume referential integrity. So the formula for the unused Dims could be:
The first part has to be a column in the DimDimension. The countrows part can be a Measure.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |