How to create a new Pivot table from existing table by applying filters in PowerBI
I am a total newbie for PowerBI. Need help on achieving the below task. I have a table as in the link: Table1
I want to create a separate table(pivot table with name Output) with column names as values from col2 of Table1 and rows from values in col3 by filtering col1 with vales containing 'real' as a substring.
The expected table (Output)from Tabe1 would be: Output
See also questions close to this topic
How to use IDs to create linlks between tables in Power BI?
I have one table, and Power BI reads it incorrectly. I decided to split one table into several different tables, and link them with IDs. But still it doesnt work.
Would you please provide some examples how to use IDs to link different tables?
Trying to link a googlesheet to PowerBI
Very basic job - I need to create some visualisations using Power BI.
However some of my data are stored on google sheets and I'm having a really hard time trying to connect them with Power Bi because
- my sheets need to continue to be dynamic (it's updated every month from different countries, so downloading the sheets for the visualisations is not really an option)
- I can't publish these sheets to the web because of privacy issues
You are my last hope guys :) Thanks
Dax Measure to count weekly series
I have a simple SUM measure that shows me how often a service was booked for the corresponding week:
Service - WK48 - WK49 A - 1 - 2 B - 1 - 0
Now I would like to determine, how many weekly series were booked for the corresponding service. That means, if a service was booked for 3 weeks in a row, that would be 1 series. The same goes for 1 week.
How would you calculate this?
The root table as a structure like: Date - Service
Each row/ entry provides a booking.
DAX Dynamic Beginning Year CAGR
I am trying to complete a dynamic CAGR formula for various services and would need it to calcuate based on when the first YEAR is not 0 or null. The beginning value shows nothing yet the ending value shows the max amount for the filtered service. Enclosed is the final table and code. Other filters have different min and max years to them.
Ending Value:=CALCULATE([Total Amount],FILTER(BH_GLOBAL_CAP,'BH_GLOBAL_CAP'[NYS_FISC_YR]=MAX(BH_GLOBAL_CAP[NYS_FISC_YR])),FILTER(BH_GLOBAL_CAP,BH_GLOBAL_CAP[TOTAL_AMT]>0)) Begining Value:= CALCULATE([Total Amount],FILTER(BH_GLOBAL_CAP,'BH_GLOBAL_CAP'[NYS_FISC_YR]=MIN(BH_GLOBAL_CAP[NYS_FISC_YR])),FILTER(BH_GLOBAL_CAP,(BH_GLOBAL_CAP[TOTAL_AMT])>0)) CAGR:=IFERROR(([Ending Value]/[Begining Value])^(1/[# of Years])-1,0) Year Max:=CALCULATE(MAX(BH_GLOBAL_CAP[NYS_FISC_YR]),FILTER(BH_GLOBAL_CAP,BH_GLOBAL_CAP[TOTAL_AMT]>0)) Year Min:=CALCULATE(MIN(BH_GLOBAL_CAP[NYS_FISC_YR]),FILTER(BH_GLOBAL_CAP,BH_GLOBAL_CAP[TOTAL_AMT]>0)) # of Years:=RIGHT([Year Max],2)- RIGHT([Year Min],2) Total Amount:=(SUM(BH_GLOBAL_CAP[TOTAL_AMT])/1000000)
Get data from slicer MDX
it's my first question here, so Hi everybody. The problem, which directed me on this page is with MDX. How can I get Date from Slicer in MDX? I need filter all rows with the slicer of date. The problem is in filtering data, I select, for example, month December. Then I'll get every row from month December. But I need to filter every row from First existing to month December. I found function PeriodsToDate(), but I need to enter the element, which in this case is "constant". I need to make this element dependent on slicer (month). Sorry for my english, I hope I expleinded my problem clear enough. Thanks in advance for Your help!
Slicers showing all values - Power BI Desktop
I have a table in Power BI Desktop which has multiple columns (5 Dimension data connected with a Fact table - Star Schema).
I have now added 3 Slicer to filter data in above table but when I select the first slicer then the other slicers should show only available dimensions data but it is showing all the available data.
I tried changing Cross filter Direction to both from single in Manage Relationship but it works for only one column but not to all with the below error or warning message.
NOTE: I do have few more Pages in the same Visualization report which contains separate fact table with same dimension keys.
Please let me know how to resolve this or any other suggestions.
Sort several bar charts and tables simultaneously in PowerBI
I am new to PowerBI and was hopping that someone could help me out.
I have several bar charts as well as tables with multiple variables for which all of them share one common column. The issue I am facing is that I want to sort the bar charts or tables based on a specific column and want the order for the rest to be updated simultaneously. Do you have any idea how I can achieve this?
Thank you in advance!
Default Date on Slicer & custom selection
I have a date slicer which should show last 12 days as selected when report opens. Further, user should have flexibility to select any other date range.