Azure DevOps pipeline to replicate Power BI report changes for various repositories (PBI workspaces)
Hello, here is my business request: -> we are having data from various countries and would like to have dedicated Power BI workspace for data from each country -> it will help us with permissions management, will clearly separate the data,... And technical setup so far looks as the following: -> as semantic models will have the same schema (table names, metric names), it means, that semantic models within each workspace will be using slightly different SQL query (which will gather data for the respective country) -> it's true, that maintenance of semantic models for multiple workspaces may be then be a little bit longer (as we will have to adapt changes for each workspace), but changes for semantic models are not frequent and usually we do bulk of changes together But I am now dealing with the technical question and what would be the best practice for development of Power BI reports, where we would like to reduce amount of duplicated work. Below is my idea for technical implementation: -> PBI reports are developed for one workspace within Azure DevOps repository, then merged through Pull request and then published into PBI workspace through Source control integration in PBI -> and I would like to have Azure pipeline, which will listen to the Pull request merge (basically any change in the master branch of that repository) and then will create similar Pull request in another repository (which is linked to another Power BI workspace), while replicating all the adapted changes for reports -> as was mentioned, schema of semantic models is the same between workspace, so it's only important for replication of Pull request with changed reports to keep proper connection (from Power BI report to semantic model), everything else can be simply copied => and my question really is, if you think, that this could be the right approach (having Azure pipelines to replicated PBI report changes into multiple repositories) and if yes, whether you could suggest to me some working Azure DevOps YAML pipelines examples ? many thanks in advance for any thoughts or suggestionsSyncing parameter of paginated report in desktop
Hi , I have the below attached filters which i need to sync and i have created parameters with creating separate data set for each parameter . The issue i am facing is while passing the parameter Metric_name in dektop with field metric_id_name then few ids are dropping . It is not passing the value and showing default All option . Metric_id_name has long text names. Metric Name is filter is working for only few ids . How can i write the parameter and query to take the exact name . or check metric id starting and see if the id is present in name . Metric_ID Metric_ID_Name MET-1509 MET-1509 - # Claims Outstanding >60 days (Receivable) MET-973 MET-973 - Compliance Risk Opinion MET-2486 MET-2486 - Outsourcing Oversight (Internal Outsourcing) MET-2488 MET-2488 - Outsourcing Oversight (Internal Outsourcing) MET-715 MET-715 - Sec Finance Income Claims Collection (>30 days) - (in millions) MET-2331 MET-2331 - PIMCO (MO) - # of impact incidents MET-1506 MET-1506 - Failed trades (for GT) MET-1037 MET-1037 - Billing Accuracy – percentage of monthly revenue - Traditional (prior prior month) MET-2603 MET-2603 - ICAMS All Incident Closure Timeliness MET-2141 MET-2141 - Failing Unit Trust and Physical Sell trades over 60 days old (Transition Settlement) Below is the Query Used on report Builder ="EVALUATE " & "VAR AsOfDate = DATE(" & Year(Parameters!SelAsOfDate.Value) & "," & Month(Parameters!SelAsOfDate.Value) & "," & Day(Parameters!SelAsOfDate.Value) & ") " & "VAR ReportedMids = CALCULATETABLE(" & "VALUES(KRI[Metric_ID]), " & "REMOVEFILTERS(KRI), " & "KRI[MetricsResults Breach Status Value] IN {""" & Join(Parameters!Breach_Status_Value.Value, """,""") & """}, " & "KRI[MetricsResults Metric Date] = AsOfDate" & ") " & "RETURN " & "SELECTCOLUMNS(" & "FILTER(" & "KRI," &"TRUE()" & IIF( UCase(Trim(Parameters!Period.Value & "")) = "3M" OR UCase(Trim(Parameters!Period.Value & "")) = "3 M", " && KRI[MetricsResults Metric Date] > EOMONTH(AsOfDate, -3) && KRI[MetricsResults Metric Date] <= AsOfDate", IIF( UCase(Trim(Parameters!Period.Value & "")) = "6M" OR UCase(Trim(Parameters!Period.Value & "")) = "6 M", " && KRI[MetricsResults Metric Date] > EOMONTH(AsOfDate, -6) && KRI[MetricsResults Metric Date] <= AsOfDate", IIF( UCase(Trim(Parameters!Period.Value & "")) = "YTD", " && KRI[MetricsResults Metric Date] >= DATE(YEAR(AsOfDate), 1, 1) && KRI[MetricsResults Metric Date] <= AsOfDate", "" ) ) )& IIF( InStr("," & Join(Parameters!Risk_unit_level1.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Risk_unit_level1.Value, ""))) = 0, "", " && (KRI[Risk_Unit_Level_1] IN {""" & Join(Parameters!Risk_unit_level1.Value, """,""") & """} || ISBLANK(KRI[Risk_Unit_Level_1]))" ) & IIF( InStr("," & Join(Parameters!Risk_Unit_level2.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Risk_Unit_level2.Value, ""))) = 0, "", " && KRI[Risk_Unit_Level_2] IN {""" & Join(Parameters!Risk_Unit_level2.Value, """,""") & """}" ) & IIF( InStr("," & Join(Parameters!Risk_Unit_Level3.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Risk_Unit_Level3.Value, ""))) = 0, "", " && KRI[Risk_Unit_Level_3] IN {""" & Join(Parameters!Risk_Unit_Level3.Value, """,""") & """}" ) & IIF( InStr("," & Join(Parameters!Classification.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Classification.Value, ""))) = 0, "", " && KRI[Metrics_KRI_Classification_Value] IN {""" & Join(Parameters!Classification.Value, """,""") & """}" ) & IIF( InStr("," & Join(Parameters!pEntityType.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!pEntityType.Value, ""))) = 0, "", " && KRI[MetricsEntity Type] IN {""" & Join(Parameters!pEntityType.Value, """,""") & """}" ) & IIF( InStr("," & Join(Parameters!pEntityName.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!pEntityName.Value, ""))) = 0, "", " && KRI[Metrics_Entity_ID_Name] IN {""" & Join(Parameters!pEntityName.Value, """,""") & """}" ) & IIF( Len(Code.CleanJoin(Parameters!Metric_Name.Value)) = 0, "", " && KRI[Metric_ID_Name] IN {""" & Code.CleanJoin(Parameters!Metric_Name.Value) & """}" ) & IIF( InStr("," & Join(Parameters!KRI_Result_Status.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!KRI_Result_Status.Value, ""))) = 0, "", " && KRI[MetricsResults Value Collection Status Value] IN {""" & Join(Parameters!KRI_Result_Status.Value, """,""") & """}" ) & IIF( InStr("," & Join(Parameters!Metric_Category_Group.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Metric_Category_Group.Value, ""))) = 0, "", " && KRI[Metric Category Group] IN {""" & Join(Parameters!Metric_Category_Group.Value, """,""") & """}" ) & IIF( InStr("," & Join(Parameters!Breach_Status_Value.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Breach_Status_Value.Value, ""))) = 0, "", " && KRI[Metric_ID] IN ReportedMids" ) & IIF( InStr("," & Join(Parameters!Region.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Region.Value, ""))) = 0, "", " && KRI[MetricsEntity_Region_Value] IN {""" & Join(Parameters!Region.Value, """,""") & """}" ) & IIF( InStr("," & Join(Parameters!Legal_Entity_Name.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Legal_Entity_Name.Value, ""))) = 0, "", " && KRI[Legal_Entity_Name] IN {""" & Join(Parameters!Legal_Entity_Name.Value, """,""") & """}" ) & IIF( InStr("," & Join(Parameters!business_or_corporate.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!business_or_corporate.Value, ""))) = 0, "", " && KRI[business_or_corporate] IN {""" & Join(Parameters!business_or_corporate.Value, """,""") & """}" ) & IIF( InStr("," & Join(Parameters!Level_2_Risk.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Level_2_Risk.Value, ""))) = 0, "", " && KRI[Level_2_Risk] IN {""" & Join(Parameters!Level_2_Risk.Value, """,""") & """}" ) & IIF( InStr("," & Join(Parameters!Level_3_Risk_Taxonomy.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Level_3_Risk_Taxonomy.Value, ""))) = 0, "", " && KRI[Operational_Risk_Level_3] IN {""" & Join(Parameters!Level_3_Risk_Taxonomy.Value, """,""") & """}" ) & IIF( InStr("," & Join(Parameters!Metrics_Measured_In_Value.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Metrics_Measured_In_Value.Value, ""))) = 0, "", " && KRI[Metrics_Measured_In_Value] IN {""" & Join(Parameters!Metrics_Measured_In_Value.Value, """,""") & """}" ) & IIF( InStr("," & Join(Parameters!Metrics_Measurement_Frequency_Value.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Metrics_Measurement_Frequency_Value.Value, ""))) = 0, "", " && KRI[Metrics_Measurement_Frequency_Value] IN {""" & Join(Parameters!Metrics_Measurement_Frequency_Value.Value, """,""") & """}" ) & IIF( InStr("," & Join(Parameters!MetricsResults_Arrears_Value.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!MetricsResults_Arrears_Value.Value, ""))) = 0, "", " && KRI[MetricsResults_Arrears_Value] IN {""" & Join(Parameters!MetricsResults_Arrears_Value.Value, """,""") & """}" )& IIF(InStr("," & Join(Parameters!Metrics_Included_in_Report_Value.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Metrics_Included_in_Report_Value.Value, ""))) = 0, "", " && KRI[Metrics_Included_in_Report_Value] IN {""" & Join(Parameters!Metrics_Included_in_Report_Value.Value, """,""") & """}" ) & IIF( InStr("," & Join(Parameters!Metrics_KRI_Reported_To_Value.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Metrics_KRI_Reported_To_Value.Value, ""))) = 0, ""," && KRI[Metrics_KRI_Reported_To_Value] IN {""" & Join(Parameters!Metrics_KRI_Reported_To_Value.Value, """,""") & """}" )& IIF( InStr("," & Join(Parameters!Metrics_KRI_Escalated_To_Value.Value, ",") & ",", ",All,") > 0 OR Len(Trim(Join(Parameters!Metrics_KRI_Escalated_To_Value.Value, ""))) = 0, "", " && KRI[Metrics_KRI_Escalated_To_Value] IN {""" & Join(Parameters!Metrics_KRI_Escalated_To_Value.Value, """,""") & """}" ) & ")," & """MetricName"", KRI[MetricsResults_MetricName]," & """MetricDate"", KRI[MetricsResults Metric Date]," & """MetricMonthYear"", FORMAT(KRI[MetricsResults Metric Date], ""MMM YYYY"")," & """MetricMonthSort"", YEAR(KRI[MetricsResults Metric Date]) * 100 + MONTH(KRI[MetricsResults Metric Date])," & """RunDate"", KRI[RunDate]," & """Risk_Unit_L1"", KRI[Risk_Unit_Level_1]," & """Risk_Unit_L2"", KRI[Risk_Unit_Level_2]," & """Risk_Unit_L3"", KRI[Risk_Unit_Level_3]," & """Entity_Region"", KRI[MetricsEntity_Region_Value]," & """Legal_Entity"", KRI[Legal_Entity_Name]," & """Buisness_or_Corporate"", KRI[business_or_corporate]," & """Level_2_Risk"", KRI[Level_2_Risk]," & """Ops_risk_Level_3"", KRI[Operational_Risk_Level_3]," & """MetricsResults_Arrears_Value"", KRI[MetricsResults_Arrears_Value]," & """MetricsMeasuredIn_Value"", KRI[Metrics_Measured_In_Value]," & """Metrics_Measurement_Frequency"", KRI[Metrics_Measurement_Frequency_Value]," & """Metrics_Included_in ReportValue"", KRI[Metrics_Included_in_Report_Value]," & """Metrics_Included_Report_Value"", KRI[Metrics_Included_in_Report_Value]," & """Metric_KRI_Escalated_To_Value"", KRI[Metrics_KRI_Escalated_To_Value]," & """ReportedValue"", KRI[Reported_Metric_value_Formatted]," & """Metric_Entity_ID_Name"", KRI[Metrics_Entity_ID_Name]," & """Metric_Entity_Type"", KRI[MetricsEntity Type]," & """Metric_Collection_Status"", KRI[MetricsResults Value Collection Status Value]," & """Metric_Id_name"", KRI[Metric_ID_Name]," & """EntityName"", KRI[MetricsEntity Name]," & """Classification"", KRI[Metrics_KRI_Classification_Value]," & """Classification_short"", KRI[KRI_Class_Short]," & """BreachStatus"", KRI[MetricsResults Breach Status Value]," & """ThresholdAmber"", KRI[Treshold Amber]," & """ThresholdRed"", KRI[Treshold Red]," & """MetricID"", KRI[Metric_ID]," & """ReportingOrder"", KRI[Reporting_Order]," & """Action Plan"", KRI[MetricsResults Management Action]," & """Root Cause"", KRI[MetricsResults RootCause]," & """MetricCategoryGroup"", KRI[Metric Category Group]," & """ThresholdDisplay"", KRI[OPs_Thresholds_Display]" & ")"
left join dax
I have two tables store and clients; no relations between them. I must not have physical relationship, I prefer virtual. And the report have a many slicers: Year, region, country from clients. So the visual have four columns. region (clients) Country (clients) sales (clients) inventory (store) and table store have year region Country inventory Dax measure: NATURALLEFTOUTERJOIN (store, clients) The principal idea is inventory is actually a measure (e.g. left outer join) to get the total in each sublevel: country, region passing the selected value from slicer. Actually I get the total from all years in measure "inventory". I don't get the context to compute (left outer join) :( . I figure out is about the missing relation between tables. Any ideas? thanks in advance.
Summarized values from Multiple Datasets in to one Matrix ?
Good Afternoon, I have six dataset tables (say A,B,C,D,E,F) that I have imported in to a PBIX File. These represent 6 different KPIs which I wish to display in a single Matrix....One of the KPIs - say Table A - is always ahead by one Month. So today Table A shows July data, while Tables B-F show up to June data only. The single matrix must contain the latest month for each Table (so there is no question of using the month Column in Table A to use for any kind of union/join with the rest). Kindly advise if my requirement can be practical implemented - or whether I should explore an alternative solution which is acceptable within the constraints I have ?create risk matrix as a heat map
Hi, I have a list of cases ranked high, medium, low priority and rating ranked very high, high, medium, low and very low. The table is Cases linked to services and service link to rating. Is it possible to create a heat map matrix which shows number of active cases ranking vs service rating?
Cards visual with reference labels on
I am trying to create the simple card visuals with reference labels on , i need to get this reference label to be show from right of card , but i am getting bottom of the main card value. i dont have reference alignment options in my power bi desktop even though i am on latest version.
Model object names must be non empty
I have been tasked with adding another dashboard to a power bi report when I imported the files from a SharePoint site, which by the way is how I have been importing the data from the beginning. I am getting this error model. Object names must be not empty so I've been searching the model and have not been able to find a non-empty object name. Can someone provide some insight to this issue? Thank you
