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.
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
Need Distinct count of Product ID based on Filter Criteria
Hi , I need help in figuring out the solution for a measure in Analysis Tabular Cube. CountProduct is the measure I am trying to create. I need to find count of products based on the below criteria ( scenarions listed in the table below) and the count should be visible at any grain when sliced. Also Grand Total should be just the total count of products for any given selection Scenario NetSupply[Existing measure : Supply- Demand ] CountProduct No Demand No Supply 0 Blank Product With Supply and no Demand or 0 Zero Demand > 0 Blank Product With no Supply and has Demand < 0 0 Product with Supply and Demand 0 1 Product with Supply and Demand <0 0 Product with Supply and Demand >0 1 Demand, Supply & Net Supply are existing measures in Cube. if a product is has 2 segment ID's and has demand and supply , I need to double count when looking at the higher grain which is why i used Summarize function to group by productid and segmentid. Below measure is showing just 0 even when demand and supply are available. CountProduct:= VAR RowCount =COUNTROWS ( FILTER ( SUMMARIZE(FILTER('SupplyTable',[Demand]>0 && [Supply]>0), 'SupplyTable'[ProductID] ,'SupplyTable'[SegmentID]), ( IF( ( (ISBLANK([Demand])=TRUE ||[Demand]=0 ) && (ISBLANK([Supply])=TRUE ||[Supply]=0) ) || ( (ISBLANK([Demand])=TRUE ||[Demand]=0) && [Supply]>0 ) ,-1 , IF ( (ISBLANK([Demand])=FALSE ||[Demand]>0 ) && (ISBLANK([Supply])=TRUE ||[Supply]=0) ,[Demand] ,[Net Supply] ) )>= 0 )) ) RETURN IF (ISBLANK(RowCount)=TRUE &&[Net Supply]<0,0,RowCount) I got solutions from amitchandak and Greg_Deckler before. Hope you can help even this time or even anyone in this forumSUMIFS in DAX without relationship
Is there any way where we can use SUMIFS in DAX without the relationship present. I have two tables Table 1 Table 2 I know for a fact that most of them would ask me to relate the table above as there is one to many relationship. If there are multiple columns and I have to do validation based on criterias then I would use SUMIFS. Can anyone help me down with the expression for bringing values from table 2 to table 1?
