topic Re: Power Bi Report Builder DAX Result different from DAX studio in Report Server

Power Bi Report Builder DAX Result different from DAX studio

Lahryco — Wed, 10 Jul 2024 08:15:30 GMT

Hi,

I am using Power Bi Report Builder to generate Dashboard. In these dashboards I need to create a table based on a parameter which give the perimeter for the dashboard. The data collection is made through Power Bi dekstop which is connect to Azure DevOps.

I collect Azure DevOps Item for all the perimeters (OASIS_Perimeter). I can have several permieters associated to the "Ligne" so I use a matrice to associate the ligne to the permieter.

The request I use does not give the same result in DAX studio and in Power Bi Report Builder

I use the following request:

// DAX Query

DEFINE

VAR __DS0FilterTable =

TREATAS(CALCULATETABLE(SUMMARIZECOLUMNS('Table Ligne'[Ligne]),PATHCONTAINS(@Ligne,'Table Ligne'[Ligne])),'Table Ligne'[Ligne])

VAR __DS0Core =

CALCULATETABLE(SUMMARIZECOLUMNS(

'Tous PO'[Changed Date],

'Tous PO'[Closed Date],

'Tous PO'[Created Date],

'Tous PO'[Description],

'Tous PO'[Due Date],

'Tous PO'[ID],

'Tous PO'[Link],

'Tous PO'[OASIS_BOOL_ATMO],

'Tous PO'[OASIS_BOOL_EnM],

'Tous PO'[OASIS_BOOL_MOEI],

'Tous PO'[OASIS_BOOL_MOEMRAC],

'Tous PO'[OASIS_BOOL_MOES],

'Tous PO'[OASIS_Code_Marché_Contributeur],

'Tous PO'[OASIS_Code_Marché_Porteur],

'Tous PO'[OASIS_COMOE],

'Tous PO'[OASIS_Criticity],

'Tous PO'[OASIS_Description_Marché_Contributeur],

'Tous PO'[OASIS_Description_Marché_Porteur],

'Tous PO'[OASIS_Entite_Charge],

'Tous PO'[OASIS_Famille_Interface],

'Tous PO'[OASIS_GO_Contributeur],

'Tous PO'[OASIS_GO_Porteur],

'Tous PO'[OASIS_Nom_Marché_Contributeur],

'Tous PO'[OASIS_Nom_Marché_Porteur],

'Tous PO'[OASIS_OBEYA],

'Tous PO'[OASIS_Origine_du_Point],

'Tous PO'[OASIS_Perimetre],

'Tous PO'[OASIS_Remontée_MOA],

'Tous PO'[OASIS_Suivi_Action],

'Tous PO'[State],

'Tous PO'[Title],

'Tous PO'[Work Item Type],

'Tous PO'[OASIS_point_remonte_en_reunion_systeme],

'Table Ligne'[Ligne]

__DS0FilterTable

)

// Nouvelle mesure calculée pour remplacer les valeurs dans la colonne [Typologie des débats en cours]

VAR __DS0PrimaryWindowedWithReplacedValues =

ADDCOLUMNS(

__DS0Core,

"Sort_Statut",

SWITCH([State],

"Clos", "0-Clos",

"A surveiller (Traitement nominal)", "1-A surveiller (Traitement nominal)",

"Ouvert", "2-Ouvert",

"Point Ouvert entre pilote (MOE/AMO)", "3-Point Ouvert entre pilote (MOE/AMO)",

"Point nécessitant un arbitrage MOA", "5-Point nécessitant un arbitrage MOA",

BLANK(), "6",

[State]

"Retard",If([State]<>"Clos",DATEDIFF('Tous PO'[Due Date],TODAY(),DAY),0),

"Nb_Statut", CALCULATE(COUNTA('Tous PO'[State]))

)

EVALUATE

__DS0PrimaryWindowedWithReplacedValues

ORDER BY

'Tous PO'[OASIS_Nom_Marché_Porteur] ASC,

[Sort_Statut] DESC,

'Tous PO'[OASIS_Criticity] DESC,

[Retard] DESC

I have tried to force the use of the relationship: USERELATIONSHIP('Tous PO'[OASIS_Perimetre], 'Table Ligne'[OASIS_Perimetre]) in the CALCULATETABLE function but the result in Power Bi Report Builder is still not good. It gives a result with relation between 'Table Ligne' and 'Tous PO' which does not exist. In DAX studio It works well.

Here the model: 'Table Ligne'[OASIS_Perimeter] is linked to all the others tables on their [OASIS_Perimeter] column.

Here the result I have by selecting the Parameter "L15E2" in 'Table Ligne'[Ligne] in DAX:

I have one single result.

Here the result I have under Power Bi Report Builder:

The result gives the full table 'Tous PO' with the selected value in parameter usin 'Table Ligne'[Ligne]. It seems the relationship is not activate anymore even with the use of USERELATIONSHIP in the CALCULTETABLE function.

It use to work for more than one years and it failed starting from the 09/07/2024.

Regards,

Joan

Re: Power Bi Report Builder DAX Result different from DAX studio

Anonymous — Thu, 11 Jul 2024 03:13:16 GMT

Hi, Lahryco

Possible causes, DAX Studio executes queries against the dataset directly, while Power BI Report Builder relies on paginated reports. The latter may introduce differences due to its specific context and rendering requirements.

Ensure that the relationship between Table Ligne[OASIS_Perimeter] and other tables is correctly defined in your Power BI model.

You can also double-check the measures and filters used in your DAX queries. Differences in filtering or calculations can lead to different results. Investigate any changes made to the data model or dataset after September 7, 2024. Changes in data or relationships can affect the results.

If possible, compare DAX query results directly in Power BI Desktop, which provides more advanced reporting capabilities, to identify any discrepancies. Consider using DAX Studio to debug and explore your data model, and then tune your queries for use in Power BI Report Builder.

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Power Bi Report Builder DAX Result different from DAX studio

bradsy — Thu, 11 Jul 2024 18:22:52 GMT

Hi Lahryco,

You say this was working and on July 9th started to go wrong? If you believe this is a regression in the Power BI service, I suggest you file a support request. They will need a repro if you can make one from sample data.

Re: Power Bi Report Builder DAX Result different from DAX studio

Lahryco — Fri, 12 Jul 2024 07:45:24 GMT

Hi Bradsy,

Thanks for you feedback. I have found a solution by changing all my requests. I have replaced the way to calculate the__DS0CoreVar, From:

VAR __DS0Core =

CALCULATETABLE(SUMMARIZECOLUMNS(

'Tous PO'[Changed Date],

'Tous PO'[Closed Date],

'Tous PO'[Created Date],

'Tous PO'[Description],

'Tous PO'[Due Date],

'Tous PO'[ID],

'Tous PO'[Link],

'Tous PO'[OASIS_BOOL_ATMO],

'Tous PO'[OASIS_BOOL_EnM],

'Tous PO'[OASIS_BOOL_MOEI],

'Tous PO'[OASIS_BOOL_MOEMRAC],

'Tous PO'[OASIS_BOOL_MOES],

'Tous PO'[OASIS_Code_Marché_Contributeur],

'Tous PO'[OASIS_Code_Marché_Porteur],

'Tous PO'[OASIS_COMOE],

'Tous PO'[OASIS_Criticity],

'Tous PO'[OASIS_Description_Marché_Contributeur],

'Tous PO'[OASIS_Description_Marché_Porteur],

'Tous PO'[OASIS_Entite_Charge],

'Tous PO'[OASIS_Famille_Interface],

'Tous PO'[OASIS_GO_Contributeur],

'Tous PO'[OASIS_GO_Porteur],

'Tous PO'[OASIS_Nom_Marché_Contributeur],

'Tous PO'[OASIS_Nom_Marché_Porteur],

'Tous PO'[OASIS_OBEYA],

'Tous PO'[OASIS_Origine_du_Point],

'Tous PO'[OASIS_Perimetre],

'Tous PO'[OASIS_Remontée_MOA],

'Tous PO'[OASIS_Suivi_Action],

'Tous PO'[State],

'Tous PO'[Title],

'Tous PO'[Work Item Type],

'Tous PO'[OASIS_point_remonte_en_reunion_systeme],

'Table Ligne'[Ligne]

__DS0FilterTable

)

To:

VAR __DS0Core =

SELECTCOLUMNS(

CALCULATETABLE('Tous PO',

__DS0FilterTable),

'Tous PO'[Changed Date],

'Tous PO'[Closed Date],

'Tous PO'[Created Date],

'Tous PO'[Description],

'Tous PO'[Due Date],

'Tous PO'[ID],

'Tous PO'[Link],

'Tous PO'[OASIS_BOOL_ATMO],

'Tous PO'[OASIS_BOOL_EnM],

'Tous PO'[OASIS_BOOL_MOEI],

'Tous PO'[OASIS_BOOL_MOEMRAC],

'Tous PO'[OASIS_BOOL_MOES],

'Tous PO'[OASIS_Code_Marché_Contributeur],

'Tous PO'[OASIS_Code_Marché_Porteur],

'Tous PO'[OASIS_COMOE],

'Tous PO'[OASIS_Criticity],

'Tous PO'[OASIS_Description_Marché_Contributeur],

'Tous PO'[OASIS_Description_Marché_Porteur],

'Tous PO'[OASIS_Entite_Charge],

'Tous PO'[OASIS_Famille_Interface],

'Tous PO'[OASIS_GO_Contributeur],

'Tous PO'[OASIS_GO_Porteur],

'Tous PO'[OASIS_Nom_Marché_Contributeur],

'Tous PO'[OASIS_Nom_Marché_Porteur],

'Tous PO'[OASIS_OBEYA],

'Tous PO'[OASIS_Origine_du_Point],

'Tous PO'[OASIS_Perimetre],

'Tous PO'[OASIS_Remontée_MOA],

'Tous PO'[OASIS_Suivi_Action],

'Tous PO'[State],

'Tous PO'[Title],

'Tous PO'[Work Item Type],

'Tous PO'[OASIS_point_remonte_en_reunion_systeme]

)

I will check to open a ticket as it seems to be a regration.

Regards,