Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Measures differing in DAX Studio and Power Bi

Hey there.

 

Have any of you had the following problem? The [Prod_DAX_Studio] measure is the one that I used on DAX Studio, and it generates the table shown in the first image attached. The [Prod] measure is a similar one that I use on my Power Bi file (shown in the second image).

 

One important thing worth noticing is that in [Prod_DAX_Studio] I've filtered the d_users table with the IdUser = 9324298, and did it in order to illustrate my problem.

 

So what am I trying to compute? Well, basically how many connections the users have had after their first 'date' stored in the d_users[DateFirstISP]. Of course, I'm using this DateFirstISP because it's possible for one to had had connections prior to their first 'date' with ISP.

 

 

Prod_DAX_Studio = -- Here, in DAX Studio, it works just fine...
VAR _Users_providers =
	DISTINCT ( d_providers_enjoyers[IdUser] )
VAR _Users_dimensao =
    	CALCULATETABLE (
		d_users,
		_Users_providers,
		d_providers_enjoyers ,
		d_users[IdUser] IN { 9324298 }
	)
VAR _Escopo_ISPs =
	ADDCOLUMNS (
		DISTINCT ( d_calendario[StartOfMonth] ),
		"@",
			CALCULATE (
				SUMX (
					_Users_dimensao,
					SUMX (
						FILTER (
							RELATEDTABLE ( f_connections_ISPs ),
							f_connections_ISPs[DateConnection] >= d_users[DateFirstISP]
						),
						f_connections_ISPs[Connections]
					)
				)
			)
	)
RETURN
	FILTER( _Escopo_ISPs, [@] > 0 )

 

 

 

Prod = -- ... however when I take the measure into my model, it does not work anymore
VAR _Users_providers = DISTINCT ( d_providers_enjoyers[IdUser] )
VAR _Users_dimensao = 
    CALCULATETABLE ( 
        d_users,
        _Users_providers,
        d_providers_enjoyers
    )
VAR _Escopo_ISPs =
    CALCULATE (
        SUMX (
            _Users_dimensao,
            SUMX (
                FILTER (
                    RELATEDTABLE ( f_connections_ISPs ),
                    f_connections_ISPs[DateConnection] >= d_users[DateFirstISP]
                ),
                f_connections_ISPs[Connections]
            )
        ),
        d_calendario
    )
RETURN
    _Escopo_ISPs

 

 

This image shows what is the expected result (which is being computed properly in DAX Studio):

Notice that in October 2023 the number is 284, just as the number computed in the following image. However, what is not computed in the following image (which is computed by the [Prod_DAX_Studio] measure) are the upcoming months, e.g., 349 in November, 716 in December... as previously shown in the above image.

 

The following image features two tables, where the first computes the correct number for both the month and for the grand total. However, it should also shows the upcoming months, and not just the month in which his first date with ISPs took place (October 26, 2023). The second table, it computes the correct value, but if I try to add any date granularity whatsoever it no longer works.

 

So here's my point: have you ever had such problem? If so, how did you work this out?

 

Thanks in advance.

2 Replies

  • What's the point of the 

    d_providers_enjoyers

    filter?  That doesn't seem to be necessary.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hey man.

      If I don't use this table as filter, then when I'm slicing the measure in a visual the result will be shown as the same for every row, and even worst I get the “Visual has exceeded the available resources” message all over the report where this measure is being computed.

       

      Here's my data modeling tab:

      Basically, the d_providers_enjoyers table is being used as a bridge to link the f_connections_ISPs[IdUser] with the d_providers[IdProvider]. As there's no direct relationship between both tables, I then have to use the said table as a bridge table.