Dax in Power Bi Duplicate issues
Hello. I must get this DAX to work to transfer a report from report builder to Power but but I am having a few PCP locations split the measure data for some reason. here is teh dax: EVALUATE VAR FilteredTable = SUMMARIZECOLUMNS( 'Providers'[PhysicianOrganization], 'Providers'[PCPLocation], 'MemMonths'[MemberID], 'Patients'[InsuranceType], 'DateDim'[Year], 'DateDim'[MonthName], FILTER( 'Patients', 'Patients'[Populations] = "GM" ), FILTER( 'Providers', 'Providers'[HFPN_GMCC_Flag] = "Yes" ), FILTER( 'Outpatient', 'Outpatient'[OfficeVisitEncounter] = "Virtual Visit" ), FILTER( 'DateDim', 'DateDim'[Year] > 2022 ) ) VAR MeasureTable = UNION( ADDCOLUMNS( FilteredTable, "Measure Type", "", "Measure Name", "Allowed PMPM", "Numerator", [AllowedCostYTD], "Denominator", [MemberMonthsYTD], "Rate", IF( ISBLANK([AllowedCostYTD]) || ISBLANK([MemberMonthsYTD]), "0.00%", FORMAT([AllowedPMPMYTD], "Currency") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 1 ), ADDCOLUMNS( FilteredTable, "Measure Type", "Acute Care", "Measure Name", "QPC 1: Admissions Rate per 1,000", "Numerator", [AdmitsYTD], "Denominator", [MemberMonths], "Rate", IF( ISBLANK([AdmitsYTD]) || ISBLANK([MemberMonths]), "0.00%", FORMAT(ROUND([Admits1000YTD], 2), "0.00") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 2 ), ADDCOLUMNS( FilteredTable, "Measure Type", "", "Measure Name", "QPC 2: 30-Day All-Cause Readmissions", "Numerator", [ReadmitsYTD], "Denominator", [AdmitsYTD], "Rate", IF( ISBLANK([ReadmitsYTD]) || ISBLANK([AdmitsYTD]), "0.00%", FORMAT([ReadmitsYTD%], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 3 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "4: Emergency Department Visits per 1,000", "Numerator", [ERVisitsYTD], "Denominator", [MemberMonthsYTD], "Rate", IF( ISBLANK([ERVisitsYTD]) || ISBLANK([MemberMonthsYTD]), "0.00%", FORMAT(ROUND([ERVisits1000YTD], 2), "0.00") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 4 ), ADDCOLUMNS( FilteredTable, "Measure Type", "Chronic Care Management", "Measure Name", "5: cannotlist Care: Hemoglobin A1c less than 8", "Numerator", [cannotlistHbA1cLessThan8Numerator], "Denominator", [cannotlistDenominator], "Rate", IF( ISBLANK([cannotlistHbA1cLessThan8Numerator]) || ISBLANK([cannotlistDenominator]), "0.00%", FORMAT([cannotlistHbA1cLessThan8Rate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 5 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "QPC 6: Controlling High Blood Pressure", "Numerator", [BloodPressureNumerator], "Denominator", [BloodPressureDenominator], "Rate", IF( ISBLANK([BloodPressureNumerator]) || ISBLANK([BloodPressureDenominator]), "0.00%", FORMAT([BloodPressureRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 6 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "Use of Imaging Studies for Low Back Pain (LBP) (Inverse Rate)", "Numerator", [LowBackPainImaging18to64Numerator], "Denominator", [LowBackPainImaging18to64Denominator], "Rate", IF( ISBLANK([LowBackPainImaging18to64Numerator]) || ISBLANK([LowBackPainImaging18to64Denominator]), "0.00%", FORMAT([LowBackPainImaging18to64InverseRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 7 ), ADDCOLUMNS( FilteredTable, "Measure Type", "Preventive Medicine", "Measure Name", "QPC 8: Annual Physical", "Numerator", [AnnualWellnessExamNumerator], "Denominator", [AnnualWellnessExamDenominator], "Rate", IF( ISBLANK([AnnualWellnessExamNumerator]) || ISBLANK([AnnualWellnessExamDenominator]), "0.00%", FORMAT([AnnualWellnessExamRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 8 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "QPC 9: Colorectal Cancer Screening", "Numerator", [ColorectalCancerScreeningNumerator], "Denominator", [ColorectalCancerScreeningDenominator], "Rate", IF( ISBLANK([ColorectalCancerScreeningNumerator]) || ISBLANK([ColorectalCancerScreeningDenominator]), "0.00%", FORMAT([ColorectalCancerScreeningRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 9 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "PC 10: Breast Cancer Screening", "Numerator", [BreastCancerScreeningNumerator], "Denominator", [BreastCancerScreeningDenominator], "Rate", IF( ISBLANK([BreastCancerScreeningNumerator]) || ISBLANK([BreastCancerScreeningDenominator]), "0.00%", FORMAT([BreastCancerScreeningRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 10 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "QPC 11: Cervical Cancer Screening", "Numerator", [CervicalCancerNumerator], "Denominator", [CervicalCancerDenominator], "Rate", IF( ISBLANK([CervicalCancerNumerator]) || ISBLANK([CervicalCancerDenominator]), "0.00%", FORMAT([CervicalCancerRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 11 ), ADDCOLUMNS( FilteredTable, "Measure Type", "Behavioral Health Management", "Measure Name", "QPC 12: Depression Screening for Adolescents & Adults", "Numerator", [DepressionScreeningNumerator], "Denominator", [DepressionScreeningDenominator], "Rate", IF( ISBLANK([DepressionScreeningNumerator]) || ISBLANK([DepressionScreeningDenominator]), "0.00%", FORMAT([DepressionScreeningRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 12 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "QPC 13: F/U for Positive Depression Screening", "Numerator", [DepressionPositiveScreenNumerator], "Denominator", [DepressionScreeningDenominator], "Rate", IF( ISBLANK([DepressionPositiveScreenNumerator]) || ISBLANK([DepressionScreeningDenominator]), "0.00%", FORMAT([DepressionPositiveScreenRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 13 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "QPC 14: F/U After Hospitalization for Mental Illness", "Numerator", [FuInNumerator], "Denominator", [FuInDenominator], "Rate", IF( ISBLANK([FuInNumerator]) || ISBLANK([FuInDenominator]), "0.00%", FORMAT([F/U Hospitalization for Mental Illness], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 14 ), ADDCOLUMNS( FilteredTable, "Measure Type", " ", "Measure Name", "QPC 15: Antidepressant Meds Management - Effective Acute Phase Treatment", "Numerator", [AntidepressantMedicationAcutePhaseNumerator], "Denominator", [AntidepressantMedicationAcutePhaseDenominator], "Rate", IF( ISBLANK([AntidepressantMedicationAcutePhaseNumerator]) || ISBLANK([AntidepressantMedicationAcutePhaseDenominator]), "0.00%", FORMAT([AntidepressantMedicationAcutePhaseRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 15 ), ADDCOLUMNS( FilteredTable, "Measure Type", " Other ", "Measure Name", "QPC 17: Dispense as Written (DAW) Rate", "Numerator", [DAWNumerator], "Denominator", [DAWDenominator], "Rate", IF( ISBLANK([DAWNumerator]) || ISBLANK([DAWDenominator]), "0.00%", FORMAT([DAWFillRate], "0.00%") ), "Base Target", BLANK(), "Stretch Target", BLANK(), "Order", 16 ) ) RETURN MeasureTable For example: Not doing it here: But creating duplicates here:
SELECT VALID DATE USING POWER BI DAX
Hi Power BI Community, Disclaimer: New to Power BI and learning is still in progress. My Team and I are working on some date to filter the schedule of employees. We are checking the log out date of the employees. But we are getting multiple dates so what we did is we tried to filter it using time difference between attendance and the scheduled date. Please see below sample. We are getting some problems on the out_is_valid column. It should only return "1" on the same schedule date as the in_is_valid column which are highlighted in green. But we are getting another "1" value which is in blue highlight. How can we only select the IN and OUT date with the same sched start and end? Below is our code. Thanks for your help.
Combining several New Measures into a Table
Is there a way to combine three New Measures into one table? Below are the codes of Volume for Quarter 1, June, and July with the filter "Wholesaler". They work respectively in Power BI. I just need to combine them in one table or one field for visualization purposes.
Calculating Commision
I would like to write a dax code that can do all of the commision rates sumiltanously or with little repetitive processes. See Commision: https://docs.google.com/spreadsheets/d/1AbWQCIPm-LzyEdSiEuJvYMc6tx5sdmmA/edit?usp=sharing&ouid=110211951767499364327&rtpof=true&sd=true See Pbix: https://drive.google.com/file/d/1KN2fmGx9hsuJaaX0lN5p0NZl_wkhliwE/view?usp=sharing Based on the document, the Commission Policy tells you how an agent's commission is calculated. To calculate an agent's commission you need to know what the agent's monthly salary is and how much sales they made for the month for the different services (application hosting, web services, infrastructure hosting). Once you have the agent's monthly salary and total monthly sales generated, you need to calculate the Salary Cover = Total monthly sales/Agent's monthly salary. Say for example, we have an agent who earns a salary of R1000 per month and the agent generates R1000 of sales for the month (of which application hosting = R500, web services = R300 and infrastructure hosting = R200). Salary cover = total monthly sales/agent's salary = R1000/R1000 = 1. This means that according to the commission table provided, the agent falls into the first category because salary cover <= 1. Based on the commission percentages in the table for the first category, the agent's commission for the month would then be R500*10% + R300*15% + R200*7% = R109 Let's say instead of R1000, this agent made R2000 worth of sales for the month (application hosting = R1000, web services = R600 and infrastructure hosting = R400). Then salary cover = total sales/agent's salary = R2000/R1000 = 2. This means that the agent would fall into the fourth category since salary cover >= 2. The agents commission would then be R1000*100% + R600*100% + R400*50% = R1800 Just one thing to note. Commission is calculated on 1 month's service revenue. So if an agent sells an annual product of 500, commission is not calculated on 500, but on 500/12. SSL commission is calculated on 2 months revenue and domains are not included. VAT needs to be subtracted before calculating the monthly service revenue as the data is VAT inclusive. Please assist?Drill Through Button - Multipe Pages
Please assist? I created a drill through button(page 3) to drill from the Cart "Invoices Paid On time if posted on time" to it's detail page. Can u use this one drill through button to drill through mutple pages(all the invoice carts on page 3) Or must i create a drill button for each of the invoices? https://drive.google.com/file/d/1CBpFLkw37E5aeYSG9RXMLTzOXUCdcn4m/view?usp=sharing
