Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Lahryco
New Member

Power Bi Report Builder DAX Result different from DAX studio

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.
Lahryco_0-1720598543789.png

 

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

Lahryco_1-1720598759463.png

I have one single result.

 
Here the result I have under Power Bi Report Builder:
Lahryco_2-1720598852127.png

 

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

3 REPLIES 3
bradsy
Microsoft Employee
Microsoft Employee

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.

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,

v-yohua-msft
Community Support
Community Support

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors