Guide: Setup a card to see current selections of your slicers
It is not uncommon that I set filters while creating my PowerBI report, forget what I selected and wonder about the figures. As I was used to work with other BI-tools, having a "current selection" box was something that I got used to and never expected to miss it so hard in PowerBI. Reading quite some entries in the forum - that were some years old already - I first created a single-line indicator showing the selected values and then switched to a multi-line variant - but still this did not feel "nice". The single-line for all selections was impractical to read, but had a handling for "no selections" - as PowerBI does not handle this directly. The multi-line was better - but did not handle the possibility of multiple selections in a field. But still, both were impractical if you want to show selections from multiple fields with the option to have multiple values selected. So here is the solution I am now working with, which is a combination of the single-line and multi-line approach - combined with a switch and a separate table. It is still not a perfect solution, as you need to define the fields that you want to show (i.e.: it is not automatically showing what is selected unless you do not tell PowerBI which fields should be shown here). Also it is just a display visual and not a space where you can also adapt filters (like e.g. in Qlik tools). However, it is still sufficient in showing the filters in your slicers In Case anyone has further improvements to this or even is crazy enough to write a custom visual - feedback welcome! 🙂 So here is how: 1. Create a new table - go to power query editor - choose "Enter data" - create a new table (e.g. "SelectableColumns") with one Column (e.g. "ColumnName") - as values, add all the Slicers you want to show in your Current Selection box 2. Create a measure in your SelectableColumns table: (this example has 3 slicers - but can easily be extended) Measure = Switch(SELECTEDVALUE(SelectableColumns[ColumnName]), "Slicer 1", IF ( COUNTROWS(ALLSELECTED(Table1[Slicer1])) <> COUNTROWS(ALL(Table1[Slicer1])), CONCATENATEX(ALLSELECTED(Table1[Slicer1]),Table1[Slicer1],","), "Unselected" ), "Slicer 2", IF ( COUNTROWS(ALLSELECTED(Table2[Slicer2])) <> COUNTROWS(ALL(Table2[Slicer2])), CONCATENATEX(ALLSELECTED(Table2[Slicer2]),Table2[Slicer2],","), "Unselected" ), "Slicer 3", IF ( COUNTROWS(ALLSELECTED(Table1[Slicer3])) <> COUNTROWS(ALL(Table1[Slicer3])), CONCATENATEX(ALLSELECTED(Table1[Slicer3]),Table1[Slicer3],","), "Unselected" ) ) 3. Create a table visual, where you add the column SelectableColumns[ColumnName] and your new Measure I just wanted to post this for any future requests, when someone is switching from another BI-tool to PowerBI and desparately looking for a way to show all the filters in an application that currently apply.
Filtering with a sub filter with multiple value selection - will not work for Report Builder PR
I am working on a Paginated report in REPORT BUILDER that the user would like to: Select date (Month/Year) then the PhysicianOrganization then the PCPLocation to have a pdf for that location only. (PhysicianOrganization must filter PCPLocation) Below the Physician Organization does not filter on the PCPLocation and it must! Dataset1 will be filtered by PhysicianOrganization, PCPLocation and Date (monthly reporting) which used month and year and PhysicianLocation Dataset which has the Physician Organization & PCPLocation values which the Parameters connect to. Below is what the report data looks like and I added the DAX fields below (this is from a CUBE or Tabular connection) I am thinking I should put the Year and Month in the PhysicianLocation dataset? I have 3 datasets that do the following: Filter the PCPLocation by PhysicianOrganization but it will not currently allow me to choose multiple PCPLocations. I have 3 tables--- Parameter short dataset: EVALUATE SUMMARIZECOLUMNS( 'Providers'[PCPLocation], 'DateDim'[Year], 'DateDim'[MonthNameAbbreviation], FILTER( 'Providers', 'Providers'[PhysicianOrganization] = @PhysicianOrganization ), FILTER( 'DateDim', 'DateDim'[Year] = VALUE(@Year) -- Convert @Year to integer && 'DateDim'[MonthNameAbbreviation] = @Month ) ) I think this DAX must be changed: PhysicianOrganization is assigned to the parameter but only These parameters are listed here: @PhysicianOrganization, @Year, =@Month 2nd Short dataset: (no query parameters are added but the PCPLOCATION points to this dataset) I think this may be the issue a I cannot get multiple choices to work) EVALUATE SUMMARIZECOLUMNS( 'Providers'[PhysicianOrganization] ) Here is dataset 3 which is the Main DATASET AND THE YEAR AND MONTH ARE HARD CODED but the other PhysicianOrganization and PCPLOcation get their Available values for the other 2 datasets. I do list all 4 filters in the dataset parameters EVALUATE VAR FilteredTable = SUMMARIZECOLUMNS( 'Providers'[PhysicianOrganization], 'Providers'[PCPLocation], 'Patients'[InsuranceType], 'DateDim'[Year], 'DateDim'[MonthNameAbbreviation], FILTER( VALUES('DateDim'[Year]), 'DateDim'[Year] = VALUE(@Year) ), FILTER( VALUES('DateDim'[MonthNameAbbreviation]), 'DateDim'[MonthNameAbbreviation] = @Month ), FILTER( VALUES('Patients'[Populations]), 'Patients'[Populations] = "HAP HFHS Employees" ), FILTER( VALUES('Providers'[PCPLocation]), 'Providers'[PCPLocation] = @PCPLocation ), FILTER( VALUES('Providers'[PhysicianOrganization]), 'Providers'[PhysicianOrganization] = @PhysicianOrganization ), FILTER( VALUES('Providers'[HFPN_GMCC_Flag]), 'Providers'[HFPN_GMCC_Flag] = "Yes" ), FILTER( VALUES('Outpatient'[OfficeVisitEncounter]), 'Outpatient'[OfficeVisitEncounter] = "Virtual Visit" ) ) RETURN ADDCOLUMNS( FilteredTable, "UniquePatients", [UniquePatients], "TotalCostYTD", [TotalCostYTD], "MemberMonthsYTD", [MemberMonthsYTD], "Admits1000YTD", [Admits1000YTD], "AnnualWellnessExamNumerator", [AnnualWellnessExamNumerator], "AnnualWellnessExamDenominator", [AnnualWellnessExamDenominator], "AnnualWellnessExamRate", [AnnualWellnessExamRate], "AntidepressantMedicationAcutePhaseNumerator", [AntidepressantMedicationAcutePhaseNumerator], "AntidepressantMedicationAcutePhaseDenominator", [AntidepressantMedicationAcutePhaseDenominator], "AntidepressantMedicationAcutePhaseRate", [AntidepressantMedicationAcutePhaseRate], "ERVisitsYTD", [ERVisitsYTD], "ERVisits1000YTD", [ERVisits1000YTD], "BloodPressureNumerator", [BloodPressureNumerator], "BloodPressureDenominator", [BloodPressureDenominator], "BloodPressureRate", [BloodPressureRate], "DAWNumerator", [DAWNumerator], "DAWDenominator", [DAWDenominator], "DAWFillRate", [DAWFillRate], "DiabetesHbA1cLessThan8Numerator", [DiabetesHbA1cLessThan8Numerator], "DiabetesDenominator", [DiabetesDenominator], "DiabetesHbA1cLessThan8Rate", [DiabetesHbA1cLessThan8Rate], "FuInNumerator", [FuInNumerator], "FuInDenominator", [FuInDenominator], "F/U Hospitalization for Mental Illness", [F/U Hospitalization for Mental Illness], "ReadmitsYTD", [ReadmitsYTD], "ReadmitsYTDRate", [ReadmitsYTD%], "AdmitsYTD", [AdmitsYTD], "AllowedCost", [AllowedCost], "AllowedCostYTD", [AllowedCostYTD], "AllowedPMPMYTD", [AllowedPMPMYTD], "BreastCancerScreeningNumerator", [BreastCancerScreeningNumerator], "BreastCancerScreeningDenominator", [BreastCancerScreeningDenominator], "BreastCancerScreeningRate", [BreastCancerScreeningRate], "ColorectalCancerScreeningDenominator", [ColorectalCancerScreeningDenominator], "ColorectalCancerScreeningNumerator", [ColorectalCancerScreeningNumerator], "ColorectalCancerScreeningRate", [ColorectalCancerScreeningRate], "ERVisitsRate", [ERVisitsYTDMoreThan5Visits1000], "ERVisitsNumerator", [ERVisitsYTDMoreThan5Visits], "VirtualNumerator", [OutpatientUtilYTD], "VirtualRate", [OutpatientUtil1000YTD], "DepressionScreeningNumerator", [DepressionScreeningNumerator], "DepressionScreeningDenominator", [DepressionScreeningDenominator], "DepressionScreeningRate", [DepressionScreeningRate], "DepressionPositiveScreenNumerator", [DepressionPositiveScreenNumerator], "DepressionPositiveScreenDenominator", [DepressionPositiveScreenDenominator], "DepressionPositiveScreenRate", [DepressionPositiveScreenRate], "CervicalCancerNumerator", [CervicalCancerNumerator], "CervicalCancerDenominator", [CervicalCancerDenominator], "CervicalCancerRate", [CervicalCancerRate], "GenericNumeratorYTD", [GenericNumeratorYTD], "GenericDenominatorYTD", [GenericDenominatorYTD], "GenericFillRateYTD", [GenericFillRateYTD], "LowBackPainImaging18to64Numerator", [LowBackPainImaging18to64Numerator], "LowBackPainImaging18to64Denominator", [LowBackPainImaging18to64Denominator], "LowBackPainImaging18to64InverseRate", [LowBackPainImaging18to64InverseRate], "September 2023 YTD", "September 2023 YTD", "Zero", 0 )Filter contex problem
Hi, I have a measure that calculates the average expense per month per selected period. I would like to avoid being filtered by other time slicers on the page. My goal is to have a bar chart with the X showing expense categories, Y showing the amount and as input value: 1. the total expense for the selected period according to the slicer (here I will select a specific year and specific month) 2. My measure returning what is the average expense per month for the selected period (here I want to return the average expense per month for the select year) I hope that I have explained myself. Currently, if I add both measures to the graph I am getting the same values because my measure is calculating the average of the selected month which ofc is the same as the total of the month. Thank you for the help! below the code of my measure and an image to explain my goal: WA Monthly = VAR AnnualAmount = CALCULATE( [Net Amount TOT], ALL( DateTable[Date]) ) VAR MonthlyTable = ADDCOLUMNS( SUMMARIZE( Merged_Final, DateTable[Year],DateTable[Month] ), "MonthlySales",[Net Amount TOT], "Wt",[Net Amount TOT]/AnnualAmount ) VAR WA_Monthly = SUMX( MonthlyTable, [Wt]*[MonthlySales] ) RETURN ABS(WA_Monthly)
Line Chart Not Filtering Correctly! Help!
Hi, I need some help. My line chart is not filtering when I click on a filter. Please see example below: Although one school is selected and one filter (A level) on the line chart all data is shown. The relationship is below and is active; I just want the line chart to show the filtered line, any help would be greatly appreciated! Thanks
DAX to show top N item name based on row count
Hi, I have a fact table where each row is labeled by columns IssueType and IssueSeverity. I need a measure that prints the most common IssueType (based on rows labeled by it) and another measure that prints row count for that issue. Also I need similar measures for 2. common and 3. common IssueTypes and row counts. I managed to do that for a table visual. It tells me top 3 IssueTypes and counts. However, I want to use separate top three items on podium-like visuals or in Smart narrative visual and then using table is not an option. I have this measure for ranking (works for table visual): RankFaults = RANKX(ALL(IssueTypesTable), [CountRows]) CountRows measure used above is: CountRows = CALCULATE(COUNTROWS(MyFactTable), MyFactTable[IssueType]<>"") I have tried something like this to pick an N item but my tries doesn't work: Top2fault = CALCULATE(FIRSTNONBLANK(IssueTypesTable[IssueType], IssueTypesTable[IssueType]), [RankFaults]=2) How to resolve this? Thanks!Calculating Direct Material from AX 2012 with DAX function
Hi, Pretty new to Power BI and DAX but I'm having problem calculating a DAX formula getting some information from Microsoft AX 2012. Trying to get the the SUM of all components with certain costgroups to present as a column in this Power BI table, which is the sales a period selected with a slicer. This SQL does the job when I have found the right "PriceCalcID": Select SUM(SALESPRICEQTY) from BOMCALCTRANS where PRICECALCID like '058256' and ( COSTGROUPID like 'Mtrl' OR COSTGROUPID like 'SF' ) Trying to this but know I'm missing something. I need first to find the latest date in the selected period. And for that certain date I need to find the PriceCalcID for the Product No in the above Power BI table. DM = VAR DMLastdate = LASTDATE(BOMCALCTRANS[TRANSDATE]) // Find the latest price change date VAR DMPriceCalcID = SELECTEDVALUE(BOMCALCTRANS[PRICECALCID]) // Find the specific ID for the latest pricechange VAR DMSumPriceSales = CALCULATE(SUM(BOMCALCTRANS[SALESPRICEQTY]), FILTER(BOMCALCTRANS, BOMCALCTRANS[COSTGROUPID]="SF" || BOMCALCTRANS[COSTGROUPID] = "Mtrl") ) Return DMSumPriceSales Thank you very much in advance 🙏 Daniel
