topic Re: Using ALL function with relationship in DAX Commands and Tips

Using ALL function with relationship

scknnylmz — Mon, 24 Jun 2024 16:38:50 GMT

Hi,

I am trying to calculate how many question completed by user. For example in this month there are 10 people and 3 of the answered question-1 and 5 of the answered question-2 so question-1 Rates should be 0.3 and question-2: 0.5

My formula like that

IF(

HASONEVALUE(DateSlicer[period])

&& SELECTVALUE(DateSlicer[period]) = "month"),

CALCULATE(CALCULATE(DISTINCTCOUNT(Table[userID]),Table[Category] = "a", Table[Questions] =MAX(Table[Questions])/CALCULATE(

DISTINCTCOUNT(Table[userID]),

Table[Category] = "a",

ALL(Table),USERELATIONSHIP(DateSlicer[DateFormats], DateTable[year]))

But in this case I am using ALL functional but it smashing to USERELATIONSHIP Function. So I can not calculate right.

How can do it with using this USERELATIONSHIP function?

Re: Using ALL function with relationship

AlexisOlson — Mon, 24 Jun 2024 17:47:50 GMT

Try only including the columns you explicitly want to be removed from the filter context inside ALL().

For example, only removing Table[Questions] filter context:

Formula = VAR _Q = MAX ( Table[Questions] ) VAR _UserCount = CALCULATE ( DISTINCTCOUNT ( Table[userID] ), Table[Category] = "a", Table[Questions] = _Q ) VAR _UserCountAll = CALCULATE ( DISTINCTCOUNT ( Table[userID] ), Table[Category] = "a", ALL ( Table[Questions] ), USERELATIONSHIP ( DateSlicer[DateFormats], DateTable[year] ) ) RETURN IF ( SELECTEDVALUE ( DateSlicer[period] ) = "month", DIVIDE ( _UserCount, _UserCountAll ) )

Re: Using ALL function with relationship

scknnylmz — Tue, 25 Jun 2024 07:07:31 GMT

Thanks,

With using ALL(), relationships is not effecting to result. Therefore, it is not working.